Forum Discussion

Narendra90's avatar
Narendra90
Frequent Visitor
3 years ago
Solved

dynamic filter criteria

Hi Team,

I have created mulitiple measures in dashboard. All measures based on different columns ( Project value, Initial value, Actual value, No of emp etc). However everyone has same filter criteria like ( country = India && year =2023 etc). 

There are chances that filters will be added some criteria in future or will be removed. In that case i need update all the measures in dashboard. 

do we have any solution on this.

Appreciate your help.

Thanks

Need Help
  • danextian's avatar
    danextian
    3 years ago

    You can create a measure and reference that in another measure

    filter measure =
    CALCULATE (
        COUNTROWS ( 'table' ),
        FILTER ( 'table', 'table'[country] = "India" && 'table'[year] = 2021 )
    )

    another measure =
CALCULATE (
    SUM ( 'table'[value] ),
    FILTER ( 'table', [filter measure] <> BLANK () )
)

     

3 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

    hi Narendra90 ,

     

    You can create a calculated column based on a certain logic and use it to filter the entire report or just a page. 

    Filter column =
'table'[country] = "India"
    && 'table'[year] = 2021

    this will return TRUE/FALSE. Use this in  the filter pane and set the value to true. Or

    Filter column =
IF ( 'table'[country] = "India" && 'table'[year] = 2021, 1, 0 )

    For the above, set the value to 1. Or if you have list of countries in another table which is updated as needed

    Filter column =
VAR _COUNTRY =
    LOOKUPVALUE (
        'country table'[country],
        'country table'[country], 'table'[country]
    )
RETURN
    IF ( NOT ( ISBLANK ( _COUNTRY ) ) && 'table'[year] = 2021, 1, 0 )

     

    The filter logic depends entirely on how you expect your data will change.

     

     

  • Narendra90's avatar
    Narendra90
    Frequent Visitor
    3 years ago

    Hey, Any other solution? cant create column here. Its connected to live data. we can request to create column once but modifing criteria multiple time would be headache to admin.

    cant we create measure and refer that in another something like this.

    • danextian's avatar
      danextian
      Super User
      3 years ago

      You can create a measure and reference that in another measure

      filter measure =
    CALCULATE (
        COUNTROWS ( 'table' ),
        FILTER ( 'table', 'table'[country] = "India" && 'table'[year] = 2021 )
    )

      another measure =
CALCULATE (
    SUM ( 'table'[value] ),
    FILTER ( 'table', [filter measure] <> BLANK () )
)