VAR and SWITCH Output issues

[Help_me]

Hello All,

I have a table with called snaphot with the columns: date (date type), action (text values that can be A, B, X, Y), project_Num (text values such as H00789).

All of these values can repeat. I want to write DAX statement that reclassifies the action values as

A -> Cheese
B -> Crackers
X -> Water
Y -> Cookie 

BUT, if the date and the project_num are the same for A and B -> Cheese/Crackers

I have written the DAX statement below, but the output rewrites the Cheese/Crackers with conditions in lines 1 & 2. Not sure how to take into account for this.

New Action =

VAR CurrentProject = snapshot[project_Num]

VAR CurrentDate = snapshot[date]

VAR HasA =

    CALCULATE(

        COUNTROWS(snapshot),

        snapshot[project_Num] = CurrentProject,

        snapshot[date] = CurrentDate,

        snapshot[action] = "A"

    ) > 0

VAR HasB =

    CALCULATE(

        COUNTROWS(snapshot),

        snapshot[project_Num] = CurrentProject,

        snapshot[date] = CurrentDate,

        snapshot[action] = "B"

    ) > 0

RETURN

SWITCH(

    TRUE(),

    -- If both A and B exist on same date

    snapshot[action] IN {"A","B"} && HasA && HasB, "Cheese/Crackers",

    -- Individual mappings

    snapshot[action] = "A", "Cheese",

    snapshot[action] = "B", "Crackers",

    snapshot[action] = "X", "Water",

    snapshot[action] = "Y", "Cookie",

    BLANK()

)

[FBergamaschi]

Hi @Help_me 

you need to remove the filters injected by CALCULATE in these variables

VAR HasA =

    CALCULATE(

        COUNTROWS(snapshot),

        snapshot[project_Num] = CurrentProject,

        snapshot[date] = CurrentDate,

        snapshot[action] = "A",
       REMOVEFILTERS ( snapshot )

    ) > 0

VAR HasB =

    CALCULATE(

        COUNTROWS(snapshot),

        snapshot[project_Num] = CurrentProject,

        snapshot[date] = CurrentDate,

        snapshot[action] = "B",
       REMOVEFILTERS ( snapshot )

    ) > 0

rest of the code seems ok to me

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[mizan2390 · Accepted solution]

Hi @Help_me 

The logic inside your SWITCH statement is actually perfectly structured. The reason your code is failing and skipping down to the individual mappings is due to a hidden DAX mechanic inside your HasA and HasB variables called Context Transition.

Because you are writing a Calculated Column, DAX evaluates the formula row by row (a Row Context). Whenever you use the CALCULATE function inside a Row Context, DAX performs a "context transition," which automatically converts the current row into a filter. This means CALCULATE silently applies a filter for every single column in that specific row. If the current row has an action of "B", DAX restricts the table to that specific row and then tries to apply your snapshot[action] = "A" filter. Because a row cannot be both "A" and "B" at the same time, COUNTROWS returns 0, meaning HasA evaluates to FALSE.

Try out this DAX

New Action = 
VAR CurrentProject = snapshot[project_Num]
VAR CurrentDate = snapshot[date]

VAR HasA =
    CALCULATE(
        COUNTROWS(snapshot),
        ALL(snapshot), -- Overrides context transition to scan the whole table
        snapshot[project_Num] = CurrentProject,
        snapshot[date] = CurrentDate,
        snapshot[action] = "A"
    ) > 0

VAR HasB =
    CALCULATE(
        COUNTROWS(snapshot),
        ALL(snapshot), -- Overrides context transition to scan the whole table
        snapshot[project_Num] = CurrentProject,
        snapshot[date] = CurrentDate,
        snapshot[action] = "B"
    ) > 0

RETURN
    SWITCH(
        TRUE(),
        
        -- If both A and B exist on same date for the same project
        snapshot[action] IN {"A","B"} && HasA && HasB, "Cheese/Crackers",
        
        -- Individual mappings
        snapshot[action] = "A", "Cheese",
        snapshot[action] = "B", "Crackers",
        snapshot[action] = "X", "Water",
        snapshot[action] = "Y", "Cookie",
        
        BLANK()
    )

If this solves your problem, please mark this as solved and give a kudos.
@meso that i don't lose this thread.

[Zanqueta]

Hi , when you use CALCULATE(), DAX performs a context transition and automatically applies the filter from the current row.
When you write:
DAXsnapshot[project_Num] = CurrentProject,snapshot[date] = CurrentDate,

snapshot[action] = "A"

Power BI is adding filters on top of the current row, including the action value of that same row.
For example, if the current row is "A", the HasB calculation is trying to check:

there is a row where
action = "A" (from the current row)
AND action = "B"

This is not possible, so it always returns FALSE.
As a result, the SWITCH applies the individual rules and replaces the expected "Cheese/Crackers" result.

Try this:

New Action =

VAR HasA =

    CALCULATE(

        COUNTROWS(snapshot),

        ALLEXCEPT(snapshot, snapshot[project_Num], snapshot[date]),

        snapshot[action] = "A"

    ) > 0

VAR HasB =

    CALCULATE(

        COUNTROWS(snapshot),

        ALLEXCEPT(snapshot, snapshot[project_Num], snapshot[date]),

        snapshot[action] = "B"

    ) > 0

RETURN

SWITCH(

    TRUE(),

    snapshot[action] IN {"A","B"} && HasA && HasB, "Cheese/Crackers",

    snapshot[action] = "A", "Cheese",

    snapshot[action] = "B", "Crackers",

    snapshot[action] = "X", "Water",

    snapshot[action] = "Y", "Cookie",

    BLANK()

)

@Help_me

[krishnakanth240]

Hi @Help_me 

Issue is context transition inside CALCULATE. HasA and HasB are not evaluating within current row context so they can override intended condition. 

Can you try using ALLEXCEPT to fix filter context so the check is done only for the same project_Num and date.

New Action =

VAR HasA =CALCULATE(COUNTROWS(snapshot),ALLEXCEPT(snapshot, snapshot[project_Num], snapshot[date]),snapshot[action] = "A") > 0

VAR HasB = CALCULATE(COUNTROWS(snapshot), ALLEXCEPT(snapshot, snapshot[project_Num], snapshot[date]),snapshot[action] = "B") > 0

RETURN

SWITCH(TRUE(),snapshot[action] IN {"A","B"} && HasA && HasB, "Cheese/Crackers",snapshot[action] = "A", "Cheese",snapshot[action] = "B", "Crackers",snapshot[action] = "X", "Water",snapshot[action] = "Y", "Cookie", BLANK())