Isinscope - varying distinctcount

[Silvard]

Please use the link below for the PBIX file.

https://drive.google.com/file/d/1MojvzPV3l4zeMF4VhbJWbOosfYoGtJaB/view?usp=drive_link

I have a graph that consists of a 3-level hierarchy.

In the first two levels, I would like to use distinctcount JobID. In the 3rd level I would like to use distinctcount ApplicationID where if Inscope(FactTable[Jobs and Applications]) is IN { "10. Verbal Offer", "11. Letter of Offer", "13. Background", "15. Commencement", "05. Verbal Offer", "06. Letter of Offer", "08. Background", "10. Commencement"}.

Otherwise, I would like to use distinctcount JobID in the 3rd level too.

I have created the below measure and tried to use this in the X-axis, but it only works for those filtered rows that need to use distinctcount ApplicationID in the 3rd level and not for those that need to use distinctcount JobID in the 3rd level.

You'll be able to see this in the PBIX.

DAX

I have included below a snip of the results that I would "roughly" expect to see when drilldown into Jobs -> Traditional. 

Expected Results

I would greatly appreciate your help as this is instrumental to the organisation.

[lbendlin]

Access to the sample data is denied, please check.

[Silvard]

Hi @lbendlin 

Can you please try again and apologise in advance?

[lbendlin]

Yes, works now.

- Your hierarchy is in the fact table. That's not where it belongs. The dimension columns need to go into the dimension tables.

- Your hierarchy has holes.  That's not optimal.  Ragged hierarchies are ok, but hierarchies with holes are not.

You may want to fix that before you proceed.

[Silvard]

Thanks a lot @lbendlin 

I've only started to use power bi just over a month ago.

There are holes in the data because I've removed some of it before sharing and it shouldn't have any impact on what we are trying to achieve, but I do appreciate that you're pointing it out.

The three columns in the hierarchy are in fact calculated columns, which is why I placed them there. If this is incorrect, please let me know.

Do you have any idea why the measure isn't working?

[lbendlin]

There are holes in the data because I've removed some of it before sharing and it shouldn't have any impact on what we are trying to achieve,

Let me rephrase it then: You cannot have holes (blank elements) in the middle of a hierarchy. Ragged hierarchies are not nice, but manageable.  Child notes with multiple parents are not nice but manageable. Holes - no.

Work on your data model, clean up the hierarchy columns, and then focus back on the measure.  ISINSCOPE is the right tool, use it from the bottom of the hierarchy upwards (rather than tops down).

[Silvard · Accepted solution]

Thanks @lbendlin for your guidance. I really appreciate it.

I have managed to find a solution  using the below DAX and structure.