Filtering on values of frequency tables

[arnebracke]

Hi there, 

I always suffer with frequency tables in Power BI. See next example:

• Activities are being logged per patient with a certain start date and duration. Raw data looks like this:
  
• I want to build a report based on the number of activities per patient (typical frequency table)
  • It should reflect any date / other filters used on the page
  • It should be possible to filter the page when selecting 1 of the frequency results,
    • E.g. Select the patients with 3 activities -> what is the effect on the average duration

This how far I got:

For the frequency calculation, I used a measure based on a supporting frequency table (definition of the different bands), but of course there is no link between this frequency table and the data -> so clicking on the "3 column" in the column chart, does'nt have any effect on the globals measures (data table is not filtered):

Is there a way to achieve this filtering funcitonality based on results in the frequency table?

Very looking forward to your answers, it kept me awake all night 😉

Link to the pbix-file

[arnebracke]

Hello Winniz, 

Thanks for your response.

Your solution works fine to filter the data table, but it does not work for any measure that are added on the page:

Measures as average duration or # activities are not adjusted when filtering by clicking the frequency chart...

Best regards,
Arne

[v-kkf-msft]

Hi @arnebracke ，

Please update the formula to:

Measure = 
VAR Count_ =
    CALCULATE (
        [#count],
        FILTER ( ALLSELECTED ( Data ), Data[Patient_ID] = MAX ( Data[Patient_ID] ) )
    )
VAR freq = SWITCH ( Count_, 1, "1", 2, "2", 3, "3", "3+" )
VAR selectfreq = SELECTEDVALUE ( FreqGroups[Freq] )
RETURN
    IF (
        selectfreq = BLANK (),
        1,
        COUNTROWS (
            FILTER ( VALUES ( Data[Patient_ID] ), freq = MAX ( FreqGroups[Freq] ) )
        )
    )

Then add this filter to each visuals:

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-kkf-msft]

Hi @arnebracke ,

Please create the measure and filter table visual when the measure is not blank.

Measure = 
VAR Count_ =
    CALCULATE (
        [#count],
        FILTER ( ALLSELECTED ( Data ), Data[Patient_ID] = MAX ( Data[Patient_ID] ) )
    )
VAR freq = SWITCH ( Count_, 1, "1", 2, "2", 3, "3", "3+" )
VAR selectfreq = SELECTEDVALUE ( FreqGroups[Freq] )
RETURN
    IF (
        selectfreq = BLANK (),
        Count_,
        COUNTROWS (
            FILTER ( VALUES ( Data[Patient_ID] ), freq = MAX ( FreqGroups[Freq] ) )
        )
    )

  

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.