CODEWITHSUNDEEP
powerbidax
Justyna MK
Justyna MK

Reputation: 3563

Return Slicer's Value (trade simulator)

I work with a single table (called sTradeSim) that I have created in PowerQuery. It has 3 columns (Fund1, Fund2, Fund3), each having values from -10 to 10, with an increment of 1.

enter image description here

I also have three separate slicers, each created using an option "Greater than or equal to". Each slicer is having a field assigned to it - Slicer 1 = Fund1, Slicer 2 = Fund2, Slicer 3 = Fund3. Below is a screenshot of Slicer 1.

enter image description here

Right next to these three slicers is a table with three rows. For each row, I would like to retrieve the value of the respective slicers. So the desired result would look like:

Unfortunately, DAX formula that I have developed is always returning 3.00 (the value of the third slicer).

enter image description here

I have tried to find a solution on the forum and combine my SWITCH formula with ALL, ALLEXCEPT, SELECTEDVALUE etc., but it seems like I'm missing something very basic.

mHV_Trades =
SWITCH(
    MAX(FundTable[FundsRanked]),
    1, MIN(sTradeSim[Fund1]),
    2, MIN(sTradeSim[Fund2]),
    3, MIN(sTradeSim[Fund3])
)

Upvotes: 0

Views: 62

Answers (1)

Joao Leal
Joao Leal

Reputation: 5542

What you are trying to do doesn't work, because essentially when you place 1 filter on any column on the table, it will filter all the rows that have that value. So, when you apply a filter fund1 = -10 it will also filter the values for fund 2 and fund 3.

You have 2 options:

  1. Create independent tables each with values from -10 to 10
  2. Create a table with all the combinations of -10 to 10 values for every fund.

For your example with 3 funds this works quite nicely (the table has about 10k records), all the combinations of -10 to 10 (21) to the power of 3, the problem with this solution is that depending on the number of funds you have you will run out of space quite quickly.

Upvotes: 3

Related Questions