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 sh...
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.

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

       

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

        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.