DAX - Cross filtering to return values

[Joe1990]

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 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 data

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

The data is in two seperate tables.

[d_gosbell]

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

[Joe1990]

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]

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.

[d_gosbell · 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.

[Joe1990]

Thanks d_gosbell, this solution worked a treat.

[v-cherch-msft]

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,