topic Re: Refer to multiple column values in DAX Commands and Tips

Refer to multiple column values

Phellipe — Mon, 05 Feb 2024 10:58:52 GMT

Hi, looking for some advice

i got this table and i need to add adittional column which will balance negative numbers - currently on line for SQL SRV 2018 Ent Core 2017 there is "-186" and new output should be 0 and row for SQL SRV 2022 Ent Core should be by 186 less (760-186)

- similary for Standard - 2019 would be 0 and 2022 version would be less by 324 (460-324)

basicaly apply downgrading licensing option

any ideas please?

Re: Refer to multiple column values

talespin — Mon, 05 Feb 2024 17:57:14 GMT

hi Phellipe ,

There must be some order(A column that makes sure that records are ordered correctly before performing calculation) to achieve this.

Re: Refer to multiple column values

Anonymous — Wed, 07 Feb 2024 09:13:16 GMT

Hi Phellipe

For your question, here is the method I provided:

Here's some dummy data

"Table"

First, add the index column in “Transform data”

Click Close & Apply.

Create a measure. Add extra columns to balance negative numbers - currently SQL SRV 2018 Ent Core 2017's rows have "-186", the new output should be 0, SQL SRV 2022 Ent Core's rows should be 186 less (760-186).

New_Delta = var _delta = IF( SELECTEDVALUE('Table'[Delta]) <> SELECTEDVALUE('Table'[Entitlements]), VALUES('Table'[Delta]), CALCULATE( ABS(MIN('Table'[Delta])), FILTER( ALL('Table'), 'Table'[Index] = MAX('Table'[Index]) -1 ) ) ) RETURN IF( SELECTEDVALUE('Table'[Delta]) > 0 && SELECTEDVALUE('Table'[Delta]) <> SELECTEDVALUE('Table'[Entitlements]), VALUES('Table'[Delta]), SELECTEDVALUE('Table'[Delta]) - _delta )

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Refer to multiple column values

Phellipe — Fri, 09 Feb 2024 11:15:53 GMT

Perfect, works like a charm 🙂