Return Slicer's Value (trade simulator)

Question

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.

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.

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:

Row No 1 = -10.00 (the value of Slicer 1),
Row No 2 = -2.00 (the value of Slicer 2),
Row No 3 = 3.00 (the value of Slicer 3).

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

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])
)

I'm not sure exactly what the issue is here but I'd suggest that if you want to control each fund independently, then the Fund1, Fund2, Fund3 columns should be on independent tables so that filtering one fund doesn't affect the other ones. — Alexis Olson
Thanks @AlexisOlson, helpful as always. That makes sense. I wanted to avoid this solution as I have around 20-25 funds in my original report, which forces me to create 25 separate tables. I have edited my original post to show why I think SWITCH formula always returns 3.00 - as you said, it is probably caused by me using a single table. I'm happy to accept your solution if you post it here; are there any alternative solutions you can think of? — Justyna MK
Requirement seems complicated to me. Sorry for that :( I think you can re visit your explanation in question and explain in steps as - First show your sample data, then show all 3 slicers sample screen shot and finally explain the output you need based on slicer selection wit logic. :) — mkRabbani
Thanks for the feedback @R_R , of course I would like everyone to understand my question :-) I have edited my original post, hopefully it's a little bit clearer now. It's almost like I want these 3 columns to ignore each other, but I cannot figure out how to do it...? — Justyna MK
I've attempted this before and failed. I don't think there's a good way to make columns operated independently of their table. That just not how tables work in Power BI, unfortunately. Independent parameters require independent tables. — Alexis Olson

Joao Leal Joao Leal · Accepted Answer · 2020-10-13T23:29:29

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:

Create independent tables each with values from -10 to 10
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.

Return Slicer's Value (trade simulator)

1 Answers