Dynamic calculation depending on slicer selection

[felipereyes_]

Hello everyone, 

I need to create a measure that calculates a score for each hospital based on a calculation that only includes hospitals within the same state.

In my data model I have a hierarchy which starts with State (DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2], city (DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL1]) and hospital (DIM_GEO_NO_RLS[DES_GEONIVEL2]). As you can see the latter comes from another table. So the state column has values like B0100, B0200, B0300 and so on, and for each value we have mutliple hospitals with their names.

I have the dax below to calculate the score of each hospital but it is based on a longer calculation considering all hospitals. So throughout the calculation it takes into account the growth of the hospital, then median of all hospitals' growths (so the median of all states) and then it groups them accordingly. But I need the calculation to only consider the median of the hospitals of the same state and in the grouping as well. Then the idea is to have a slicer for the visualization with which I can choose which state with its hospitals should be displayed.

So basically the calculation should be dynamic, only taking into account the hospitals in the selected state, so the score of each hospital is based on state level calculation.

I know the dax is a bit complicated, since it takes the growth of each hospital and adds some calculation like the median of all growths, then groups the individual growths and adds a new calculation. But I think its the first part in table_ that is decisive to just calculate the median and do the grouping for the hospitals of the state selected.

 VAR table_ =

    FILTER(

        ADDCOLUMNS(ALL(DIM_GEO_NO_RLS[LINEA], DIM_GEO_NO_RLS[ID_TERRNIVEL1], DIM_GEO_NO_RLS[DES_GEONIVEL2])

    , "Growth", CALCULATE([CM% Hibor TTO 3M Informe no_rls Felipe] - [CM% HIBOR TTO 3M PREV NO_RLS FELIPE], REMOVEFILTERS(DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL1_FILTER], DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2_FILTER]), REMOVEFILTERS(DIM_GEO_NO_RLS[DES_GEONIVEL3_FILTER], DIM_GEO_NO_RLS[DES_GEONIVEL4_FILTER]))

        )

    , DIM_GEO_NO_RLS[LINEA] = "B" && DIM_GEO_NO_RLS[ID_TERRNIVEL1]<>54)

VAR table_2 =

    ADDCOLUMNS(table_

    , "Median Growth", MEDIANX(table_, [Growth])

    )      

VAR table_3 =

    ADDCOLUMNS(table_2

    , "Max Growth G1", MAXX(filter(table_2, [Growth] >= [Median Growth]), [Growth])

    , "Min Growth G1", MINX(filter(table_2, [Growth] >= [Median Growth]), [Growth])

    , "Max Growth G2", MAXX(filter(table_2, [Growth] < [Median Growth]), [Growth])

    , "Min Growth G2", MINX(filter(table_2, [Growth] < [Median Growth]), [Growth])

    )  

VAR table_4 =

    ADDCOLUMNS(table_3

    , "Hibortt 40%", IF([Growth] >= [Median Growth], DIVIDE(([Growth]-[Min Growth G1])*5, ([Max Growth G1] - [Min Growth G1]), 0) + 5, DIVIDE(([Growth]-[Min Growth G2])*5, ([Max Growth G2] - [Min Growth G2]), 0))

)

VAR table_5 =

    ADDCOLUMNS(table_4

    , "Valor Tier B", [Hibortt 40%]

    )

VAR table_6 =

    ADDCOLUMNS(table_5

    , "Ranking Tier B", RANKX(table_5, [Valor Tier B], [Valor Tier B], DESC, Skip)

    )

VAR table_7 =

    ADDCOLUMNS(table_6

    , "MAX Ranking Tier B", MAXX(table_6, [Ranking Tier B])

    )

VAR _SelectedTerritory = SELECTEDVALUE(DIM_GEO_NO_RLS[DES_GEONIVEL2])

RETURN MINX(FILTER(table_7, [DES_GEONIVEL2]=_SelectedTerritory), [Valor Tier B])

I would really appreciate any kind of help!!

[v-zhouwen-msft]

Hi @felipereyes_ ,

Regarding your question, put your 'state' field into the slicer and try to modify your 'table_' variable.This should be able to filter by state before calculating the median.

 VAR table_ =
    FILTER(
        ADDCOLUMNS(ALL(DIM_GEO_NO_RLS[LINEA], DIM_GEO_NO_RLS[ID_TERRNIVEL1], DIM_GEO_NO_RLS[DES_GEONIVEL2])
    , "Growth", CALCULATE([CM% Hibor TTO 3M Informe no_rls Felipe] - [CM% HIBOR TTO 3M PREV NO_RLS FELIPE], REMOVEFILTERS(DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL1_FILTER], DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2_FILTER]), REMOVEFILTERS(DIM_GEO_NO_RLS[DES_GEONIVEL3_FILTER], DIM_GEO_NO_RLS[DES_GEONIVEL4_FILTER]))
        )
    , DIM_GEO_NO_RLS[LINEA] = "B" && DIM_GEO_NO_RLS[ID_TERRNIVEL1]<>54) && DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2] = SELECTEDVALUE(DIM_TERRITORIO_NO_RLS[DES_TERRNIVEL2])