Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.

 

 
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? 
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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.

5 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      That was really helpful. Thanks a lot!

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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