Sliced per day, count rows for the corresponding week and month

[Jonathan_P]

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

[mattbrice]

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] ) ) ) 

[Jonathan_P · Accepted solution]

@mattbrice,

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] ) ) )

[Jonathan_P]

@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?

[Jonathan_P]

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!

[v-yulgu-msft]

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