Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
@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/
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
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
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
I hope that clarifies!
Cheers
Andy
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |