topic New calculated column too complex for me . in DAX Commands and Tips

New calculated column too complex for me .

paololito — Sun, 14 Aug 2022 16:38:58 GMT

Hello ,

I hope that you can help me on this issue because I'm little disapointed about it .

My level in Dax is not sufficient to archive this .

I have a table 'NewPBI" I would like create a calculated column "NewAmount" with this king of approach

let me explain with some examples

Situation 1 : Scenario A = Scenario B and only one row for the combinaison Scenario A and G/L# so newAmount should be Amount

situation 2 : Scenario A <> Scenario B and only one row for the combinaison Scenario A and G/L# so newAmount should be the amount of the combination Scenario A and G/L#

Situation 3 : multiple result for the combination of Scenario A and G/L #

Scenario A = Scenario B then NewAmount = Amount

Scenario A <> Scenario B the NewAmount = 0

the dax formula seems very complex so i hope that you can help me on this .

Thank you very much

Paololito

Re: New calculated column too complex for me .

latimeria — Sun, 14 Aug 2022 17:43:28 GMT

Hi paololito ,

My understanding of your problem:
scenario A & G/L : unique => amount
not unique, scenario A = scenario B => amount else 0

Data, copy in powerquery

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckwuKU3MUTAyMDRS0kHjGRobGhgYABm6hiAGkBmrQ0iHEVSHKRb1JijqjVFtwGaBGYoGUxQN2CwwI9kLqDrMUHUQtsIUxc+GEPWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Scenario A" = _t, #"Scenario B" = _t, #"G/L" = _t, Amount = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Amount", type number}}) in #"Changed Type"

Dax formula for column:

New Amount = VAR CurrentScenarioGL = 'Table'[Scenario A] & 'Table'[G/L] VAR CountScenarioGL = CALCULATE ( COUNTROWS ( 'Table' ), REMOVEFILTERS ( 'Table' ), 'Table'[Scenario A] & 'Table'[G/L] = CurrentScenarioGL ) VAR NewAmount = SWITCH ( TRUE (), CountScenarioGL = 1, 'Table'[Amount], 'Table'[Scenario A] = 'Table'[Scenario B], 'Table'[Amount], 0 ) RETURN NewAmount

Pay attention, 'switch' stop evaluation when first condition is met.

Hope this help.

Re: New calculated column too complex for me .

paololito — Sun, 14 Aug 2022 19:25:10 GMT

latimeria Thank you very much . indeed this is very usefull.

I didn't understand yet your formula but , I will take time to understand it for sure .

However I forgot to put another scenario , sorry for that .

in the situation below Scenario A = Actual 2017 and Scenario B = Actual 2016 NewValue should contain the last known value when scenario A = Scenario B

the purpose of this script is to have a value for each scenario A.

In this situation the last value is Scenario A = Actual 2016 and Scenario B = Actual 2016

I hope you can help me again , I cross my finger 😉

paololito

Re: New calculated column too complex for me .

latimeria — Sun, 14 Aug 2022 22:17:49 GMT

Try this:

New Amount = VAR MaxScenarioBForCurrentGroup = CALCULATE ( MAX ( 'Table'[Scenario B] ), ALLEXCEPT ( 'Table', 'Table'[Scenario A], 'Table'[G/L] ) ) VAR NewAmount = // choose expressions - both give same result //IF ( 'Table'[Scenario B] = MaxScenarioBForCurrentGroup, 'Table'[Amount], 0 ) CALCULATE( SUM('Table'[Amount]), 'Table'[Scenario B] = MaxScenarioBForCurrentGroup ) RETURN NewAmount

Re: New calculated column too complex for me .

paololito — Mon, 15 Aug 2022 14:07:36 GMT

latimeria It works fine . thank you very much for your support .

your dax code seems be very simple 😉

I choose the IF expression because the other one don't give the result expected .

Re: New calculated column too complex for me .

paololito — Thu, 18 Aug 2022 15:11:05 GMT

latimeria , I try to understand your code .

Can you give me some explanation about the usage of ALLEXCEPT .

Normally ALLEXCEPT mean ALL (table) except filter context but I have no filter context on my table . does it mean that when you calculate Row by Row the value of a column became a filter context ?

Re: New calculated column too complex for me .

latimeria — Fri, 19 Aug 2022 07:28:01 GMT

Hi paololito ,

Calculate works only with filters context, not with row context, so you"re right the row context is tranformed into a filter context.

you can find the explantion here: Understanding context transition in DAX - SQLBI

Steps for creating new column with calculate: (not all steps explained, there more steps) for each row:

save the current filter context
transition the row context into filter context. basically, filter all fields with the current field's value
apply the new context, allexcept = remove (not filter added!) any active filters on all fields except on columns Table'[Scenario A] & 'Table'[G/L] .
get the max value for 'Table'[Scenario B] in this new context, with current '[Scenario A] & [G/L]
restore the current context

The most important thing to remind is that the evaluation happens after filering.

screen shot after the calculate:

usefull links:
ALLEXCEPT – DAX Guide

Introducing CALCULATE in DAX - SQLBI