Forum Discussion

Help_me's avatar
Help_me
Frequent Visitor
4 months ago
Solved

VAR and SWITCH Output issues

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 r...
  • mizan2390's avatar
    mizan2390
    4 months ago

    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's avatar
Zanqueta
Super User
4 months ago

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