Forum Discussion
Problem with RANKX and ISINSCOPE returning only 1 on every level
I'm attaching a pbix file with some mock data but below are prints of my actual file and error, I'm truly at my wits end and cannot figure out why this measure is not working:
Dax:
_1 Ranks =
VAR is_inscope_tipo = ISINSCOPE('TAB_BD_UNIFICADA13'[Tipo/Conjunto])
VAR is_inscope_familia = ISINSCOPE('TAB_BD_UNIFICADA13'[Família Equip.])
VAR is_inscope_equipamento = ISINSCOPE('TAB_BD_UNIFICADA13'[Equipamento/Local (Onde)])
VAR is_inscope_item = ISINSCOPE('TAB_BD_UNIFICADA13'[Item/Componente])
VAR ranking = SWITCH(
TRUE(),
is_inscope_tipo,
RANKX(ALLSELECTED('TAB_BD_UNIFICADA13'[Tipo/Conjunto]),
[_Impacto_Total]
),
is_inscope_familia,
RANKX(ALLSELECTED('TAB_BD_UNIFICADA13'[Família Equip.]),
[_Impacto_Total]
),
is_inscope_equipamento,
RANKX(ALLSELECTED('TAB_BD_UNIFICADA13'[Equipamento/Local (Onde)]),
[_Impacto_Total]
),
is_inscope_item,
RANKX(ALLSELECTED('TAB_BD_UNIFICADA13'[Item/Componente]),
[_Impacto_Total]
),0
)
RETURN
ranking
Link to drive with pbix:
https://drive.google.com/file/d/1hImXInqrsJgEfSQvcFU_5M1wsu5AAgLw/view?usp=sharing
pls try this
you did the ISINSCOPE order in SWITCH incorrectly, you need to start from the bottom_1 Ranks = VAR is_inscope_tipo = ISINSCOPE('TAB_BD_UNIFICADA13'[Tipo/Conjunto]) VAR is_inscope_familia = ISINSCOPE('TAB_BD_UNIFICADA13'[Família Equip.]) VAR is_inscope_equipamento = ISINSCOPE('TAB_BD_UNIFICADA13'[Equipamento/Local (Onde)]) VAR is_inscope_item = ISINSCOPE('TAB_BD_UNIFICADA13'[Item/Componente]) VAR ranking = SWITCH( TRUE(), is_inscope_item, RANKX(ALLSELECTED('TAB_BD_UNIFICADA13'[Item/Componente]), [_Impacto_Total],,DESC,Dense ), is_inscope_equipamento, RANKX(ALLSELECTED('TAB_BD_UNIFICADA13'[Equipamento/Local (Onde)]), [_Impacto_Total],,DESC,Dense ), is_inscope_familia, RANKX(ALLSELECTED('TAB_BD_UNIFICADA13'[Família Equip.]), [_Impacto_Total],,DESC,Dense ), is_inscope_tipo, RANKX(ALLSELECTED('TAB_BD_UNIFICADA13'[Tipo/Conjunto]), [_Impacto_Total],,DESC,Dense ) ,0 ) RETURN ranking
4 Replies
pls try this
you did the ISINSCOPE order in SWITCH incorrectly, you need to start from the bottom_1 Ranks = VAR is_inscope_tipo = ISINSCOPE('TAB_BD_UNIFICADA13'[Tipo/Conjunto]) VAR is_inscope_familia = ISINSCOPE('TAB_BD_UNIFICADA13'[Família Equip.]) VAR is_inscope_equipamento = ISINSCOPE('TAB_BD_UNIFICADA13'[Equipamento/Local (Onde)]) VAR is_inscope_item = ISINSCOPE('TAB_BD_UNIFICADA13'[Item/Componente]) VAR ranking = SWITCH( TRUE(), is_inscope_item, RANKX(ALLSELECTED('TAB_BD_UNIFICADA13'[Item/Componente]), [_Impacto_Total],,DESC,Dense ), is_inscope_equipamento, RANKX(ALLSELECTED('TAB_BD_UNIFICADA13'[Equipamento/Local (Onde)]), [_Impacto_Total],,DESC,Dense ), is_inscope_familia, RANKX(ALLSELECTED('TAB_BD_UNIFICADA13'[Família Equip.]), [_Impacto_Total],,DESC,Dense ), is_inscope_tipo, RANKX(ALLSELECTED('TAB_BD_UNIFICADA13'[Tipo/Conjunto]), [_Impacto_Total],,DESC,Dense ) ,0 ) RETURN rankingFirst off, your measure works, but I found something else out, I have a filter on my visual to not show blank values, when putting your measure in it with this filter on it looks like this:
(Show values when isn't blank)
No filter (correct).
Which kinda sucks. Anyways, thanks for the help.I can't say specifically what the problem is, but I think the blank here is also shadowed, so they need to be excluded.
Have you tested to see what is actually in scope on each row of the matrix?
Suggest you create 4 test measures (1 for each isinscope) and add them to the matrix. You'll then be able to confirm if your main measure is doing what you think it is. And if not, possibly a rewrite of the SWITCH is required.
