Forum Discussion

MilanRao06's avatar
MilanRao06
Advocate I
9 years ago
Solved

Using Slicer values in a calculated column

Hi All,   I am building a dashboard for the quarterly customer satisfaction feedback scores. In my data model, i have the columns Quarter, Customer Name and the feedback details.    My usecase to...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi MilanRao06,

     

    The value of a calculated table or calculate column is computed during data refresh, it does not depend on user interaction in the report. So it is not possible to create a dynamic calculate table or calculate column depend on user selections.

     

    In this scenario, you may need to create a measure similar like below, and show the Contact IDs with the created measure in a Table or Matrix on the report.

    IsCommonQuarter =
IF (
    ISFILTERED ( Response[Quarter] ) && HASONEVALUE ( Response[Quarter] ),
    IF ( LASTNONBLANK ( Response[Quarter], 0 ) = [CommonQuarter], 1, 0 )
)

    Note: You may also need to use a separate table column of "Quarter" as the Slicer instead of Response[Quarter] column in the same table.

     

    Regards

tringuyenminh92's avatar
tringuyenminh92
Memorable Member
9 years ago

Hi MilanRao06,

 

What is formula to calculate for "common respondents"? and kind of data type of feedback details? Could you please provide sample data and expectation as picture? so I could understand your situation correctly. 

MilanRao06's avatar
MilanRao06
Advocate I
9 years ago

Hi tringuyenminh92,

 

Thanks for the response. Explaining my use-case - 

 

I have a table called Response with the following fields. As you can see, I have a few contacts who have responded to the survey in 2016-Q1 and 2016-Q2.

In my dashboard, I have a slicer for Quarters. If the user, selects 2016-Q2, I want the list of contacts in Q2 who had also responded in Q1. This can either in the same table with a flag for the common respondent or the Contact IDs of the common respondents in a different table.

 

What I have done till now -

Based on the slicer selection, I have been able to get the value of the 'Selected Quarter'-1 in a Calculated Measure called 'CommonQuarter'.

 

I am then trying to create a dynamic table with the following DAX formula-

 

Table = CALCULATETABLE(Response,FILTER(Response,Response[Quarter]=[CommonQuarter]))

 

But this returning a blank table with all the columns of the Response table. However, if I hardcode the quarter instead of the measure 'CommonQuarter' as below, I am getting the appropriately populated table. 

 

Table = CALCULATETABLE(Response,FILTER(Response,Response[Quarter]="2016-Q2"))

 

Hope you are able to understand my problem statement. 

 

Thanks,

Milan

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

    Hi MilanRao06,

     

    The value of a calculated table or calculate column is computed during data refresh, it does not depend on user interaction in the report. So it is not possible to create a dynamic calculate table or calculate column depend on user selections.

     

    In this scenario, you may need to create a measure similar like below, and show the Contact IDs with the created measure in a Table or Matrix on the report.

    IsCommonQuarter =
IF (
    ISFILTERED ( Response[Quarter] ) && HASONEVALUE ( Response[Quarter] ),
    IF ( LASTNONBLANK ( Response[Quarter], 0 ) = [CommonQuarter], 1, 0 )
)

    Note: You may also need to use a separate table column of "Quarter" as the Slicer instead of Response[Quarter] column in the same table.

     

    Regards

  • pavithra12's avatar
    pavithra12
    Regular Visitor
    7 years ago

    Hi Milan!

     

    I am facing the same problem that you have mentioned! Did you figure out the solution? I am quite not sure if we can use the dynamic slicer selection measure to filter the table.

     

    Any help would be great! Thanks!