Forum Discussion

mdemarne's avatar
mdemarne
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Change column computation based on a slicer

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!
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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!

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      mdemarne
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      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's avatar
        parry2k
        Icon for Super User rankSuper User
        6 years ago

        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!