Switch, True & ISINSCOPE

[andybamber]

Hi Everyone!

I have the following measure, which depending on what level of the organisation is selected in the slicer will show on a clustered column and line visual either the Headcount Budget or blank/nothing. The reason being is that we only had budget set at domain level, and i wanted to ensure when someone selects below this level they would see no data for headcount budget.

This has now changed and for cc_domain = "non-domain" we have headcount budgets set for the obs_level3, what I cant work out is how to change the code below to show headcount budget at obs_level3 where cc_domain = "non-domain", but not where cc_domain <> "non-domain"

Headcount_Budget_Wkly_Snap =

VAR HC = ""

VAR HCB = CALCULATE([Headcount Budget])

VAR obs_l7 =

    ISINSCOPE(OBS_MAPPING[obs_level7])

VAR obs_l6 =

    ISINSCOPE(OBS_MAPPING[obs_level6])

VAR obs_l5 =

    ISINSCOPE(OBS_MAPPING[obs_level5])

VAR obs_l4 =

    ISINSCOPE(OBS_MAPPING[obs_level4])

VAR obs_l3 =

    ISINSCOPE(OBS_MAPPING[obs_level3])

VAR domain_sel =

    ISINSCOPE ( CC_OWNERS_LN[cc_domain] )

RETURN

    SWITCH (

        TRUE (),

        obs_l7,  HC,

obs_l6,  HC,

obs_l5,  HC,

obs_l4,  HCA,

obs_l3, HCB,

domain_sel, HCB,

HC    )

Any thoughts, is it even still possible to use a varient of the above code for this new scenario?

Cheers

Andy

[amitchandak]

@andybamber , Create two measure var like this and use them

CALCULATE([Headcount Budget],Table[cc_domain] = "non-domain")
CALCULATE([Headcount Budget],Table[cc_domain] <> "non-domain")

Also, refer :https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[andybamber]

Hi There @amitchandak , thanks for this... i'm not entirely sure that would be the solution, wouldn't this just include or exclude the actual non-domain numbers rather than not display them.... maybe these images might help describe what I am looking for: The first image shows data at the the Domain level (there are 9 domain names, and one of those domain names is 'non-domain') and you can see the headcount budget line, as expected

Domain Level

When you drill down from 'Non-Domain' (which is a domain) to the next level (L3) we can also see the headcount budget line, which for Non-Domain L3 is what I require

Non-Domain - L3

The issue is that when i drill down on say the Business Support domain to L3 I also see the headcount budget line, this isnt what I want to see, i only want to see the headcount budget line for L3 for non-domain as above

Domain L3

I hope that clarifies!

Cheers

Andy