cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rakarakk Frequent Visitor
Frequent Visitor

Filter on aggregated measure

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.

 

Capture.PNG

 
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? 
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Filter on aggregated measure

Hi @rakarakk ,

 

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

 

14.PNG15.PNG

 


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.

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.

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Filter on aggregated measure

Hi @rakarakk ,

 

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

 

14.PNG15.PNG

 


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.

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.

View solution in original post

rakarakk Frequent Visitor
Frequent Visitor

Re: Filter on aggregated measure

That was really helpful. Thanks a lot!

rakarakk Frequent Visitor
Frequent Visitor

Re: Filter on aggregated measure

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:

Capture3.PNG

 

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

Capture.PNG

With filter value < 10:

Capture2.PNG

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

 

Community Support Team
Community Support Team

Re: Filter on aggregated measure

Hi @rakarakk ,

 

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.

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.
rakarakk Frequent Visitor
Frequent Visitor

Re: Filter on aggregated measure

Hello, 

 

thanks a lot for the clarification. That helps. 

 

Best regards

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)