Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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])