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.
I am trying to get the average rate that a Diagnosis is selected across Connected Device and Product Type. I will have slicers that will filter the table based on different product dimensions ie Product Launch Year, Series, Size etc. I do not want the Product Dimension slicers to impact the average rate. The only column that should impact the rate is the date filter.
Volume of Diagnosis Code per Connected Device / Product Type
= CALCULATE(COUNTROWS('Cases'),
ALLEXCEPT('Cases','Cases'[Connected Device],'Cases'[DateTimeOpened],'Cases'[Product Type],'Cases'[Diagnosis]))
Total Volume of Records per Connected Device / Product Type
CALCULATE(COUNTROWS('Cases'),
ALLEXCEPT('Cases','Cases'[Connected Device],'Cases'[DateTimeOpened],'Cases'[Product Type]))
I used ALLEXCEPT function under the impression that the slicers for the product dimensions would not affect the values, but it does not seem to be working. Any ideas as to how I can work around this?
Solved! Go to Solution.
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.
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 @wildmight2017,
Briefly glancing at it, it looks like this may be the problems I am running into. It is all starting to make some sense now. I will look into it in more depth.
Thank you!
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |