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.
Hey,
I have a dataset and am writing DAX to calculate failure rates etc per 10,000 assets over mulitple years.
The following works for the TOTAL but not once I have it broken down into categories and Fiscal Years.
So I use the below, but as you would expect, returns values based on the 2019 data.
Solved! Go to Solution.
I would suggest changing your model structure
First I would delete both of those bi-directional relationships and link the 'Pole Population Data' table to your Calendar table.
Then I would create a "Pole Type" table (which has a distinct list of all the pole types) and then create a one to many relationship from Pole Type to both 'Pole Population Data' and PoleFailureDB (with the Pole Type table on the one side of both relationships). Then hide the Pole Type columns in both 'Pole Population Data' and PoleFailureDB and only ever use the Pole Type columns from the Pole Type table.
This should make it a lot easier to get the results you want.
It's really hard to say without seeing your data model, and without being able to see the numerator and denominator values. But I think what might be happening is that because you are referencing the SUM of a column inside the divide measure it is giving you the grand total pole count across all time for the denominator for the first expression. Either setting up the pole count as a measure or wrapping it in a CALCULATE expression might fix your issue.
eg
= DIVIDE([Total Unassisted Failures], CALCULATE( SUM('Pole Population Data'[Pole Count]) ) )*10000
Thanks for the quick reply d_gosbell.
Your example didn't seem to work out in my data- FYI, it gives the same results as it did with out the CALCULATE).
I believe it could be data structure- I have fiscal year and count in another table that im trying to link to show correctly in the cross table (matrix).
Back to the drawing board, I'll keep trying and keep you posted.
Update..
I tried using:
I would suggest changing your model structure
First I would delete both of those bi-directional relationships and link the 'Pole Population Data' table to your Calendar table.
Then I would create a "Pole Type" table (which has a distinct list of all the pole types) and then create a one to many relationship from Pole Type to both 'Pole Population Data' and PoleFailureDB (with the Pole Type table on the one side of both relationships). Then hide the Pole Type columns in both 'Pole Population Data' and PoleFailureDB and only ever use the Pole Type columns from the Pole Type table.
This should make it a lot easier to get the results you want.
Thanks d_gosbell, this solution worked a treat.
Hi @Joe1990
Please check below measure.Here is the article about USERELATIONSHIP function.If it is not your case,please follow the How to Get Your Question Answered Quickly to post your simple assumed data and expected output.It would be better if you can upload the .pbix file to OneDrive and post the link here. Do mask sensitive data before uploading.
Measure = DIVIDE ( [Total Unassisted Failures], CALCULATE ( SUM ( 'Pole Population Data'[Pole Count] ) * 10000, USERELATIONSHIP ( 'Pole Population Data'[Fiscal Year], PoleFailureDB[Fiscal Year Ending] ) ) )
Regards,
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |