Forum Discussion
Resolver IIsinscope - varying distinctcount
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.
6 Replies
- lbendlin
Super User
Access to the sample data is denied, please check.
- 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.
