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

DAX - Cross filtering to return values

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.

 

= DIVIDE([Total Unassisted Failures],SUM('Pole Population Data'[Pole Count]))*10000

Returned data from first DAX measureReturned data from first DAX measure

So I use the below, but as you would expect, returns values based on the 2019 data.

 

= CALCULATE(DIVIDE([Total Unassisted Failures],SUM('Pole Population Data'[Pole Count])),'Pole Population Data'[Fiscal Year] = 2019)*10000
FY19 Data Correct, but the rest are wrong based on them limited to use the FY population dataFY19 Data Correct, but the rest are wrong based on them limited to use the FY population data
Has anyone got any ideas on how to show this data correctly?
 
The data is in two seperate tables.
 
 
1 ACCEPTED 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.

View solution in original post

6 REPLIES 6
d_gosbell
Super User II
Super User II

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.

Joe1990
Frequent Visitor

Update..

 

I tried using:

test 2 using crossfilter = CALCULATE(DIVIDE([Total Unassisted Failures],SUM('Pole Population Data'[Pole Count]))*10000,
CROSSFILTER('Pole Population Data'[Fiscal Year],PoleFailureDB[Fiscal Year Ending],Both),
CROSSFILTER('Pole Population Data'[Pole Type Description (Mapped)],PoleFailureDB[Pole Type],Both))
 
and 
 
test case (fiscal year relationship) = CALCULATE(DIVIDE([Total Unassisted Failures],SUM('Pole Population Data'[Pole Count]))*10000,
USERELATIONSHIP('Pole Population Data'[Fiscal Year],PoleFailureDB[Fiscal Year Ending]))
 
and 
 
test case (pole type relationship) = CALCULATE(DIVIDE([Total Unassisted Failures],SUM('Pole Population Data'[Pole Count]))*10000,
USERELATIONSHIP('Pole Population Data'[Pole Type Description (Mapped)],PoleFailureDB[Pole Type]))
 
Still not providing the correct answer.
 
Below is data model view.
image.png

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.

View solution in original post

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,

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors