Filter on aggregated measure

[Anonymous]

Hello,

I need to use a filter on the aggregation of a column. I need to achieve two things with this filter: 

- It should be possible to aggregate the column in different ways, and the filter should always work on the current level of aggregation. I can achieve this if I use a measure instead of a column, but then I cannot use the measure to filter at page level instead of at visual level. 

- I need to apply that filter to a second visual that has the same aggregation levels as the first one but that shows a different measure. 

To be more specific, I attach a file where the data and a page with two visuals is contained:

https://drive.google.com/open?id=1vXBSL4Xchto5swl7qq2iHDlxWV1Wibx3

I have built two columns as: 

n_articles = CALCULATE(sum(Tabelle1[n_events]),Tabelle1[event_type] == "article_produced")

n_errors = n_errors = if(Tabelle1[event_type] <> "article_produced", Tabelle1[n_events], BLANK())

There is also the measure n_errors_per_article that needs to be calculated as:

n_errors_per_article = sum(Tabelle1[n_errors])/sum(Tabelle1[n_articles])

I need to be able to filter at page level (not at visual level) on "sum of n_articles", where the aggregation level for "n_articles" changes as the user drills down the hierarchy levels or as the user applies other filters to the data.

Now, if I apply the filter n_articles > 15, I expect that the last bar of both plots disappear, because at the current aggregated level, the last category is the only one not fulfilling the conidtion n_articles > 15. Instead, both complete plots disappear. Can you help me with this? 

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

We can not make the field filter in page level with an aggregated value, but we can provide a workaround based on your description:

1. create a calculated table to filter:

Filter_N_Articles_Table =
GENERATESERIES ( 0, SUM ( Tabelle1[n_articles] ), 1 )

2. Then we create measure used in the chart:

Sum_N_Articles = 
VAR result =
    SUM ( 'Tabelle1'[n_articles] )
RETURN
    IF ( result IN FILTERS ( 'Filter_N_Articles_Table'[Value] ), result, BLANK () )

Sum_Error_Articles =
IF (
    SUM ( 'Tabelle1'[n_articles] ) IN FILTERS ( 'Filter_N_Articles_Table'[Value] ),
    SUM ( Tabelle1[n_errors] ),
    BLANK ()
)

n_errors_per_article =
IF (
    SUM ( Tabelle1[n_articles] ) IN FILTERS ( Filter_N_Articles_Table[Value] ),
    DIVIDE ( SUM ( Tabelle1[n_errors] ), SUM ( Tabelle1[n_articles] ), 0 ),
    BLANK ()
)

3. Then we can use the Value Field of Filter_N_Articles_Table in the Page Level Filter

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

That was really helpful. Thanks a lot!

[Anonymous]

Hello @v-lid-msft 

I need to generate some cards now that show the total number of articles, errors and errors per article after applying the filter. This is best illustrated with this screenshots: 

Without filter, everything looks fine:

With filter value > 30, the results is not as expected: 

With filter value < 10:

I understand that it doesnt work as I would like because the filter is applied directly to the cards. Instead, I would like that the cards refer to the content of the other visuals. Can you help me with this?

Here is the link to the file: pbix_file   

Thanks a lot

[v-lid-msft]

Hi @Anonymous ,

Sorry for that, the reason is that the card visual will calculate for total, but the column chart will aggrerate and filter for each level, we can use formula to let this card visual show correct value when the column chart is in level computer_char_3, but it is impossible to let it change when drill down to next level in column chart because the card visual cannot know the drill level of other chart.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello, 

thanks a lot for the clarification. That helps. 

Best regards