Forum Discussion
ALLEXCEPT
Try to read up on bridge tables. Such as this - https://www.sqlbi.com/daxpuzzle/multiple-many-to-many-relationships/ - this should help you to get started
HI RA
In theory, what you have described should be working perfectly, so there might be something subtly different with your report. Can you share a cut down (obfuscated) version?
I have many measures and slicers configured the way you describe and they work perfectly.
Hi Phil_Seamark,
I am using 2 data sources.
Sample of first data source:
CaseNumber DateTimeOpened Model Diagnosis 123 1/1/2018 Model A Issue 1 456 1/2/2018 Model B Issue 2 789 1/3/2018 Model C Issue 3 234 1/4/2018 Model D Issue 3 567 1/5/2018 Model A Issue 2 890 1/6/2018 Model B Issue 3 345 1/7/2018 Model C Issue 3 678 1/8/2018 Model D Issue 2 901 1/9/2018 Model A Issue 3 321 1/10/2018 Model B Issue 1 654 1/11/2018 Model C Issue 2 987 1/12/2018 Model D Issue 3 The second data source contains a lookup table to retrive the product dimensions:
Model Launch Year Series Connected Device Product Type Size Model A 2017 Series 1 Yes Type 1 10 Model B 2017 Series 2 No Type 2 20 Model C 2018 Series 1 Yes Type 1 30 Model D 2018 Series 2 No Type 2 40 I am using the RELATED function to add the product dimensions into the first data source.
Assuming '2017' is selected in the Product Year slicer, I would expect to see Model C and Model D removed from the table (but not from the measure results)
Model A with Issue 1
Diagnosis Volume: I would expect to see a count of all Connected Device 'Yes' and Product Type 'Type 1' with Diagnosis 'Issue 1'
Total Volume: I would expect to see a count of all Connected Device 'Yes' and Product Type 'Type 1' (All Issue types)
Model Diagnosis Diagnosis Volume Total Volume Rate Model A Issue 1 1 6 17% Model B Issue 2 2 6 33%
But what I actually see:
The measure value results do not include Model C and D.
Model Diagnosis Diagnosis Volume Total Volume Rate Model A Issue 1 1 3 33% Model B Issue 2 1 3 33% HI RA
If I understand your requrements properly then this PBIX file must be close
https://1drv.ms/u/s!AtDlC2rep7a-ox0ymg0j-_RLHoms
I don't use ALLEXCEPT or RELATED, so take a look and let me know what you think.
Hi Phil_Seamark,
Thank you so much for the time and effort you have put into this. Unfortunately, this solution will not work for my needs. There are hundreds of Connected device/ Product Type/ Diagnosis combinations and I would need to see the rate across all of them.
