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.
Hello,
The user wants a report where he will be able to select a day (slice per day). Then, on a matrix, he wants to see the number/count (measure) of analysis result (fact). He also wants to see it for the corresponding week and month for him to compare. By example, if he selects the date 2017-03-29, he wants to see the data for that day, for the week of 2017-03-26 and for the month of March 2017.
How do I write the measures?
Of course, it involves COUNTROWS. But, I have hard time figuring out the filters to pass to CALCULATE.
Thanks
Solved! Go to Solution.
By profiling the queries to SQL Server, I found that a part was missing in the measures Week Measure and Month Measure.
My measures are as follow:
Week Measure = CALCULATE ( [Count Analysis Result], FILTER ( ALL(Calendar), Calendar[Week Number] = MAX( Calendar[Week Number] ) ), FILTER ( ALL(Calendar), Calendar[Year] = MAX( Calendar[Year] ) ) )
Month Measure = CALCULATE ( [Count Analysis Result], FILTER ( ALL(Calendar), Calendar[Month Number] = MAX( Calendar[Month Number] ) ), FILTER ( ALL(Calendar), Calendar[Year] = MAX( Calendar[Year] ) ) )
If you have a best practice calendar table, just 3 simple mesaures should do it:. First is when slicer filters down to a day
Count Analysis Result = DISTINCTCOUNT ( Table[Analysis Result] ) (you can change this to COUNTROWS, COUNT, etc depending on need)
Week Measure = CALCULATE ( [Count Analysis Result], FILTER ( ALL(Calendar), Calendar[Week Number] = MAX( Calendar[Week Number] ) ) ) (You'll need a "Week Number" column added to Calendar table)
Month Measure = CALCULATE ( [Count Analysis Result], FILTER ( ALL(Calendar), Calendar[Month Number] = MAX( Calendar[Month Number] ) ) )
By profiling the queries to SQL Server, I found that a part was missing in the measures Week Measure and Month Measure.
My measures are as follow:
Week Measure = CALCULATE ( [Count Analysis Result], FILTER ( ALL(Calendar), Calendar[Week Number] = MAX( Calendar[Week Number] ) ), FILTER ( ALL(Calendar), Calendar[Year] = MAX( Calendar[Year] ) ) )
Month Measure = CALCULATE ( [Count Analysis Result], FILTER ( ALL(Calendar), Calendar[Month Number] = MAX( Calendar[Month Number] ) ), FILTER ( ALL(Calendar), Calendar[Year] = MAX( Calendar[Year] ) ) )
@mattbrice, thank you for the measures!
However, I forgot to say that I am working in DirectQuery mode. So, I receive the following error message after writing the "Week Measure".
Function 'FILTER' is not supported in this context in DirectQuery mode.
Any workaround for this?
I'm ok, I found the following thread that resolved my problem.
https://community.powerbi.com/t5/Desktop/Filter-is-not-working-in-Direct-query-mode/td-p/133492
@mattbrice, thanks again for the measures!
Hi @Jonathan_P,
Glad to hear you have resolved your problem. Please kindly mark the correspoind reply as an answer so that some other community members having similar concern can find the solution more easily.
Thanks,
Yuliana Gu
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 |
---|---|
105 | |
97 | |
80 | |
66 | |
62 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |