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

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

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!