Using Slicer values in a calculated column

[MilanRao06]

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

[richbenmintz]

have a look at the following post from the sqlbi

http://www.daxpatterns.com/new-and-returning-customers/.

you can use this pattern to create a measure that does what you want

[tringuyenminh92]

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]

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]

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! 

[v-ljerr-msft · Accepted solution]

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