cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RA
Frequent Visitor

ALLEXCEPT

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?

1 ACCEPTED 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

View solution in original post

6 REPLIES 6
Phil_Seamark
Microsoft
Microsoft

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.

 

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

RA
Frequent Visitor

Hi @Phil_Seamark,

 

I am using 2 data sources.

 

Sample of first data source:

CaseNumberDateTimeOpenedModelDiagnosis
1231/1/2018Model AIssue 1
4561/2/2018Model BIssue 2
7891/3/2018Model CIssue 3
2341/4/2018Model DIssue 3
5671/5/2018Model AIssue 2
8901/6/2018Model BIssue 3
3451/7/2018Model CIssue 3
6781/8/2018Model DIssue 2
9011/9/2018Model AIssue 3
3211/10/2018Model BIssue 1
6541/11/2018Model CIssue 2
9871/12/2018Model DIssue 3

 

 

The second data source contains a lookup table to retrive the product dimensions:

ModelLaunch YearSeriesConnected DeviceProduct TypeSize
Model A2017Series 1YesType 110
Model B2017Series 2NoType 220
Model C2018Series 1YesType 130
Model D2018Series 2NoType 240

 

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)

ModelDiagnosisDiagnosis VolumeTotal VolumeRate
Model AIssue 11617%
Model BIssue 226

33%

 

But what I actually see:

The measure value results do not include Model C and D.

ModelDiagnosisDiagnosis VolumeTotal VolumeRate
Model AIssue 11333%
Model BIssue 21333%

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.  

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

RA
Frequent Visitor

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

View solution in original post

RA
Frequent Visitor

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!

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.