Change column computation based on a slicer

[mdemarne]

Hi, 

I have a table T with two columns, A and B. Depending on a slicer (S), I would like to have a third column, X, with different values:

1. If S = 1, then X = B
2. if S = 0, then X = A - B
3. if S is not selected, then X = A

For this, I created a new table F with a simple column S that has two values (1 and 0), and added a column to T as follows:

X= IF(SELECTEDVALUE(F[S], -1) = 1, T[B], IF(SELECTEDVALUE(F[S], -1) = 0, T[A] - T[B], T[A]))

I created a slicer on top of F[S]. However, my selection is always ignored in the column X in table T. Note that there is no relationship between the two tables, since one essentially has two columns (T has A and B) and the other one only one (F has S).

How can I make the slicer selection stick?

Thanks!

[parry2k · Accepted solution]

@mdemarne add as a measure

X= 
VAR __s = (SELECTEDVALUE(F[S], -1) 
RETURN
SWITCH ( __s,
1, SUM ( T[B] )
0, SUM ( T[A] ) - SUM ( T[B] ),
SUM ( T[A] )
)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[mdemarne]

Thanks for the quick reply. What if I wanted to use the column to do min, max, sum, median, etc.? I could add a measure for each, but it feels like I could just do with a column and then add measures on top? Thanks.

[parry2k]

@mdemarne I don't see any value to add columns, it is going to increase the size of your model and will have a performance impact, Measures is the way to go.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!