Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: DAX - Cross filtering to return values

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

DAX - Cross filtering to return values

05-19-2019
10:28 PM

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

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

Has anyone got any ideas on how to show this data correctly?

The data is in two seperate tables.

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-20-2019
09:44 PM

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.

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-19-2019
11:05 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-20-2019
12:20 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-20-2019
03:00 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-20-2019
09:44 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-20-2019
10:18 PM

Thanks d_gosbell, this solution worked a treat.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

05-20-2019
07:55 PM

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.*

If this post

Announcements

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

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

283 | |

141 | |

111 | |

96 | |

73 |

Top Kudoed Authors

User | Count |
---|---|

283 | |

147 | |

147 | |

123 | |

94 |