Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Proud to be a Super User!
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.
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
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!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |