Forum Discussion

aj1107's avatar
aj1107
Advocate I
9 years ago
Solved

dynamic calculated column based n slicer text value

I got a below scenario. slicer value will be distinct of col1 and col2 and then pass slicer selection value to plan col1 and col2 and create a dynamic calculated column

 

id,issue,plan

1,av,av

2,av,rm

3,rm,av

 

slicer -

distinct(union(issue,plan))

av

rm

 

when slicer value is selected -> case when plan like value(slicer) then "direct" else "indirect"  (if the selected value is available in plan column then dislay direct else indirect)

 

slicer = av

 

table output

id,issue,plan, calc

1,av,av , direct

2,av,rm , indirect

3,rm,av , direct

 

slicer = rm

 

id,issue,plan, calc

2,av,rm , direct

3,rm,av , indirect

 

i tried to implement the  approach using dax. created cal measure  of selected slicer value

selected value= if(hasonevalue(slicer),values(slicer),blanlk()

 

 not sure passing the selected slicer value to create calc column.

 

attchaed code snippet of sql query of above approach.

 

 

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi aj1107,

     

    Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.

     

    So it is not possible to create a calculate column/table can change dynamically with user selections on the report.

     

    In this scenario, you can create a measure instead, then show the measures on the Table/Matrix visual with the corresponding columns from your table. The formula below to create the measure is for your reference. :smileyhappy:

    measure = 
IF (
    HASONEVALUE ( Table2[slicer] ),
    IF (
        VALUES ( Table2[slicer] ) = FIRSTNONBLANK ( Table1[plan], 1 ),
        "direct",
        "indirect"
    )
)

    Regards

1 Reply

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi aj1107,

     

    Not like measures, calculate columns/tables are computed during database processing(e.g. data refresh) and then stored in the model, they do not response to user selections on the report.

     

    So it is not possible to create a calculate column/table can change dynamically with user selections on the report.

     

    In this scenario, you can create a measure instead, then show the measures on the Table/Matrix visual with the corresponding columns from your table. The formula below to create the measure is for your reference. :smileyhappy:

    measure = 
IF (
    HASONEVALUE ( Table2[slicer] ),
    IF (
        VALUES ( Table2[slicer] ) = FIRSTNONBLANK ( Table1[plan], 1 ),
        "direct",
        "indirect"
    )
)

    Regards