topic Re: ISINSCOPE Query in DAX Commands and Tips

ISINSCOPE Query

andybamber — Mon, 02 Nov 2020 08:27:19 GMT

Hello All

I have a report, and have a headcount target at Domain level (this is the top level of 5). The report has a slicer which allows the user to view headcount at these various levels. I have a line chart which shows actual headcount over time... I also want to show headcount target but obviously only when the user is looking at the top Domain level. I thought if i created a measure below for the headcount target, and used isinscope to determine the level selected it would show actual headcount target at the Domain level, and then when they move below that it would show the Headcount measure (which means the actual and budget would display the same on the visual)... however it doesnt, and however low the user goes in the hierarachy it also shows the domain headcount target

Headcount_Budget_Wkly_Snap = SWITCH ( TRUE (), ISINSCOPE ( Domain_Table[cc_domain] ), [Headcount_Target], ISINSCOPE(OBS_MAPPING[obs_level3]), [Headcount], ISINSCOPE(OBS_MAPPING[obs_level4]), [Headcount],ISINSCOPE(OBS_MAPPING[obs_level5]), [Headcount] )

Thanks

Andy

Re: ISINSCOPE Query

amitchandak — Mon, 02 Nov 2020 09:46:50 GMT

andybamber , is showing headcount target at this level Domain_Table[cc_domain] on Job level 3,4,5 it should show Headcount . Is this what you need ?

See if this document can help : https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

Can you share sample data and sample output in table format?

Re: ISINSCOPE Query

Anonymous — Mon, 02 Nov 2020 10:11:37 GMT

ISINSCOPE need to be structured in a very specific way to work correctly. The most granular/detailed level must be first, then the second most granular... and so on.

Re: ISINSCOPE Query

andybamber — Mon, 02 Nov 2020 11:32:54 GMT

thanks Anonymous ... i tried that and it still doesnt ... stupid question probably, but does the hierarchy have to be structured as a Hierarchy? at the moment i have the Domain level from 1 table and the other Obs_Levels are from a different table, although there are obviously relationships in place to join them all, otherwise the slicer wouldnt work

Cheers

Andy

Re: ISINSCOPE Query

andybamber — Mon, 02 Nov 2020 11:40:56 GMT

thanks amitchandak you are correct, that is how i need it to work... I looked at that link you gave me and I dont seem to be able to make that work, also tried daxer's suggestion to order from most granular to least and that doesnt work either... ill try and put together a sample file for you to look at...

Andy

Re: ISINSCOPE Query

Anonymous — Mon, 02 Nov 2020 12:35:14 GMT

Please have a look at this: https://www.daxpatterns.com/hierarchies/

Re: ISINSCOPE Query

Anonymous — Mon, 02 Nov 2020 12:53:49 GMT

You can also read this: https://www.sqlbi.com/articles/distinguishing-hasonevalue-from-isinscope/

Re: ISINSCOPE Query

andybamber — Tue, 03 Nov 2020 10:00:55 GMT

Thanks Anonymous amitchandak so i realised why it wasnt working, the visual didnt contain the domain or obs levels, that said i was hoping it would be able to work based off selections from the filter slicer.... when i applied to a visual which had domain and obs, it worked as expected when i drill through... but i guess the question is whether you can do a similar thing based off slicer selections?

Thanks

Andy

Re: ISINSCOPE Query

Anonymous — Tue, 03 Nov 2020 11:13:26 GMT

Read this: https://www.sqlbi.com/articles/distinguishing-hasonevalue-from-isinscope/