Forum Discussion

dlopesc04's avatar
dlopesc04
Resolver II
1 year ago
Solved

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 ...
Need Help
  • Ahmedx's avatar
    Ahmedx
    1 year ago

    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

     

HotChilli's avatar
HotChilli
Community Champion
1 year ago

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.