New calculated column too complex for me .

[paololito]

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

[latimeria]

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.

[paololito]

@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

[latimeria · Accepted solution]

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

[paololito]

@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 ?

[latimeria]

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

[paololito]

@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 .