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 identify the common respondents in a particular quarter. I have a Slicer for the quarter. If I choose 2016-Q3 as the the slicer value, then I want to check the common respondents of Q2 and Q3. 

 

Basically, I want a column in my table to update as 0 or 1 where 1 is the common respondent in that quarter.

 

Please help...

 

Regards,

Milan Rao

 

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

5 Replies

  • 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