Forum Discussion

ruxandraalina's avatar
ruxandraalina
Helper I
3 years ago
Solved

Dynamic ranking based on filter context

Hello, 

I've been struggling to create a ranking that shows the county with the highest/lowest value based on my filter context, as shown below:

 

I need to create two cards that show (1) the county with the highest value and the actual value and (2) the county with the lowest value and the actual value. These two cards need to be dynamic, depending on what filters the end user is selecting: it can be absolutely no filter, maybe only the Year filter or maybe 1 Year + 1 Category or 1 Year + multiple categories, etc. 

 

I'd be grateful for any suggestion.

Thank you!

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi ruxandraalina ,

    Please try to create two measure with below dax formula:

    Highest Value =
VAR _year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR _str =
    CONCATENATEX ( 'Table', [Category] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        [Year] = _year
            && CONTAINSSTRING ( _str, [Category] )
    )
VAR _max =
    MAXX ( tmp, [Value] )
VAR _val =
    CALCULATE ( MAX ( 'Table'[Country] ), FILTER ( tmp, [Value] = _max ) )
RETURN
    _val

    Lowest Value =
VAR _year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR _str =
    CONCATENATEX ( 'Table', [Category] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        [Year] = _year
            && CONTAINSSTRING ( _str, [Category] )
    )
VAR _min =
    MINX ( tmp, [Value] )
VAR _val =
    CALCULATE ( MAX ( 'Table'[Country] ), FILTER ( tmp, [Value] = _min ) )
RETURN
    _val

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi ruxandraalina ,

    Please try to create two measure with below dax formula:

    Highest Value =
VAR _year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR _str =
    CONCATENATEX ( 'Table', [Category] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        [Year] = _year
            && CONTAINSSTRING ( _str, [Category] )
    )
VAR _max =
    MAXX ( tmp, [Value] )
VAR _val =
    CALCULATE ( MAX ( 'Table'[Country] ), FILTER ( tmp, [Value] = _max ) )
RETURN
    _val

    Lowest Value =
VAR _year =
    SELECTEDVALUE ( 'Table'[Year] )
VAR _str =
    CONCATENATEX ( 'Table', [Category] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        [Year] = _year
            && CONTAINSSTRING ( _str, [Category] )
    )
VAR _min =
    MINX ( tmp, [Value] )
VAR _val =
    CALCULATE ( MAX ( 'Table'[Country] ), FILTER ( tmp, [Value] = _min ) )
RETURN
    _val

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.