topic Re: Calculated Column in DAX Commands and Tips

Calculated Column

bob57 — Mon, 01 Aug 2022 14:30:50 GMT

Hello,

I'm hoping that someone can provide me with the DAX to accomplish the following

Thank you for your time,

Bob

Here is the raw data:

I need to add the calculated column, Difference:

Pseudo code: Rating - Base Rating for each match:

Re: Calculated Column

tamerj1 — Mon, 01 Aug 2022 14:41:18 GMT

Hi bob57

howdid you calculate the Difference?

Re: Calculated Column

bob57 — Mon, 01 Aug 2022 15:07:07 GMT

I did it by hand for demonstration purposes. But the algorithm would go like this:

For each Match (1, 2, and 3) subtract the Base Rating (Rating where Base = yes) from the Rating and round to the nearest whole.

The calculation must be independent for the match so for Match = 2, for example:

when Tee = Red, then 65.4 - 69.7 = -4

when Tee = White, then 69.7 – 69.7 = 0

when Tee = Blue, then 71.3 – 69.7 = 2

I hope this offers the required clarity.

Bob

Re: Calculated Column

bob57 — Mon, 01 Aug 2022 15:08:58 GMT

My apologies. I neglected to mention that the calculation must be rounded to the nearest whole number.

Re: Calculated Column

tamerj1 — Mon, 01 Aug 2022 15:26:43 GMT

Hi bob57
Here is a sample file with the solution https://we.tl/t-KWLD6ZeAMf

Difference = VAR CurrentBase = Data[Base] VAR CurrentRating = Data[Rating] VAR CurrentMatchYesRating = CALCULATE ( MAX ( Data[Rating] ), ALLEXCEPT ( Data, Data[Match] ), Data[Base] = "yes" ) VAR Result = IF ( CurrentBase = "no", CurrentRating - CurrentMatchYesRating, 0 ) RETURN ROUNDDOWN ( Result, 0 )

You may need to readjust the rounding type as per your requirement.

Re: Calculated Column

bob57 — Mon, 01 Aug 2022 15:33:58 GMT

That did the trick! Thank you so much.