topic Re: DAX Measure Error In One Row In A Matrix in DAX Commands and Tips

DAX Measure Error In One Row In A Matrix

Grizzlydad — Tue, 18 Aug 2020 14:23:17 GMT

Hiya I have written a measure to calculate the time saved by "NILO" compared to "Region" and all seems well in the visual except in the month of June when the calculated difference is incorrect?

Here is the measure

NILO Saving (Minutes) = CALCULATE ( SUM ( Tbl_NationalRawData[Minutes Taken To Respond]), Tbl_NationalRawData[Region Responded]= "REGION" ) - CALCULATE ( SUM ( Tbl_NationalRawData[Minutes Taken To Respond]), Tbl_NationalRawData[NILO Responded]= "NILO" )

The matrix uses the following data but in the row for June, the calculation is wrong.

Month NILO Region

July 295 102

June 105 572

May 88 190

April 169 344

March 155 104

The NILO Saving figure for June in the matrix visual returns 559 when it should be 467 (essentially the difference between the two). The visulisation rows use Month and the Value is NILO Saving (Minutes), which is the measure.

All the other months return the correct difference in figures except June?

I have multiple tabs using the same matrix for each region and each one has the wrong difference beingreturned for June only.

Can anyone help out please?

Thanks

Andrew

Re: DAX Measure Error In One Row In A Matrix

Pragati11 — Tue, 18 Aug 2020 14:31:32 GMT

Hi Grizzlydad ,

Try modifying your DAX expression as follows:

NILO Saving (Minutes) = CALCULATE ( SUM ( Tbl_NationalRawData[Minutes Taken To Respond]), FILTER(ALLSELECTED(Tbl_NationalRaw), Tbl_NationalRawData[Region Responded]= "REGION" )) -

CALCULATE ( SUM ( Tbl_NationalRawData[Minutes Taken To Respond]), FILTER(ALLSELECTED(Tbl_NationalRaw), Tbl_NationalRawData[NILO Responded]= "NILO" ))

Thanks,

Pragati

Re: DAX Measure Error In One Row In A Matrix

AntrikshSharma — Tue, 18 Aug 2020 15:15:59 GMT

Can you provide the PBIX file? without that it is difficult to tell why you get the error.

Re: DAX Measure Error In One Row In A Matrix

Grizzlydad — Tue, 18 Aug 2020 15:29:36 GMT

Hi thanks, that has made each month have the same total now which is the total minutes saved year to date, not individual month totals.

Re: DAX Measure Error In One Row In A Matrix

Grizzlydad — Tue, 18 Aug 2020 15:38:46 GMT

Re: DAX Measure Error In One Row In A Matrix

Anonymous — Wed, 19 Aug 2020 05:55:12 GMT

Hi Grizzlydad ,

Try Troubleshooting your measures

Create Measures

Measure 1 Region = CALCULATE ( SUM ( Tbl_NationalRawData[Minutes Taken To Respond]), Tbl_NationalRawData[Region Responded]= "REGION" )

Measure2 NILO = CALCULATE ( SUM ( Tbl_NationalRawData[Minutes Taken To Respond]), Tbl_NationalRawData[NILO Responded]= "NILO" )

Difference = Measure 1 Region - Measure2 NILO

See what values are returned by all these 3 measures separately.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: DAX Measure Error In One Row In A Matrix

Grizzlydad — Wed, 19 Aug 2020 06:29:04 GMT

Hi

Where do you want me to try this troubleshooting, I don't understand?

Re: DAX Measure Error In One Row In A Matrix

Anonymous — Wed, 19 Aug 2020 07:08:03 GMT

Hi Grizzlydad ,

Have you created these measures.

Not against the Month Column in the Visualization Pane, what is the value of thes measures.

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)