How to convert a CASE WHEN to DAX to create a calculated table?

[ParsaJafarian]

Hi,

I've been told with the help of the forum and ChatGPT that a `CASE WHEN` SQL statement can be translated as such to DAX:

FILTERED_TABLE= 
VAR vEmployee = SELECTEDVALUE(SlicerTable[Employee])
VAR vCurrent = SELECTEDVALUE(SlicerTable[Current])
VAR vActive = SELECTEDVALUE(SlicerTable[Active])

RETURN SWITCH( TRUE(),
    vEmployee = "Employee" && vCurrent = "Current" && vActive = "Active & TSOS", 
    FILTER(TABLE, TABLE[DATE_ID] = 20250505 && TABLE[EMPLOYEE_CURRENT_ACT_TSOS_NBR] = 1),
    FALSE()
)

However, I get the following error:

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Because I am returning an entire table as the result in the SWITCH statement. Is there any way of filtering TABLE with the use of a SWITCH, IF or any other method in DAX? I have come at a loss. Thanks.

[ParsaJafarian · Accepted solution]

Hi, 

I realized that I couldn't solve the issue with DAX so instead I created dimension tables that filtered the fact table through relationship. So for instance, there would be relationships such that when

vEmployee = "Employee" && vCurrent = "Current" && vActive = "Active & TSOS"

 , then the Table would be filtered through 

TABLE[EMPLOYEE_CURRENT_ACT_TSOS_NBR] = 1

[v-pgoloju]

Hi @ParsaJafarian,

Thank you for reaching out to the Microsoft Fabric Forum Community.
And also Thanks to @FarhanJeelani  and @bhanu_gautam  for Prompt and useful response.

Try Using This DAX:

Filtered_Table =
VAR vEmployee = SELECTEDVALUE(SlicerTable[Employee])
VAR vCurrent = SELECTEDVALUE(SlicerTable[Current])
VAR vActive = SELECTEDVALUE(SlicerTable[Active])

RETURN
IF(
vEmployee = "Employee" && vCurrent = "Current" && vActive = "Active & TSOS",
FILTER(
TABLE,
TABLE[DATE_ID] = 20250505 &&
TABLE[EMPLOYEE_CURRENT_ACT_TSOS_NBR] = 1
),
BLANK()
)

Let us know if you need further assistance we’re happy to help. If this addressed your query, please consider marking the response as an Accepted Solution so it can assist others in the community as well.

Thank you & Regards,
Prasanna kumar

[FarhanJeelani]

Hi @ParsaJafarian ,
The core issue is that SWITCH(TRUE(), ...) is meant to return scalar values, not entire tables. In your case, you're trying to return a filtered table from inside SWITCH, which DAX doesn't allow in that way.

If you're trying to create a calculated table based on logic similar to a CASE WHEN, the best approach is to use VARs to define conditions, then apply them in a FILTER() like this:

FILTERED_TABLE =
VAR vEmployee = SELECTEDVALUE(SlicerTable[Employee])
VAR vCurrent = SELECTEDVALUE(SlicerTable[Current])
VAR vActive = SELECTEDVALUE(SlicerTable[Active])

RETURN
FILTER (
    TABLE,
    (
        vEmployee = "Employee"
        && vCurrent = "Current"
        && vActive = "Active & TSOS"
        && TABLE[DATE_ID] = 20250505
        && TABLE[EMPLOYEE_CURRENT_ACT_TSOS_NBR] = 1
    )
)

If you have multiple condition combinations
You can build more complex logic with nested IFs instead of SWITCH, like this:

FILTERED_TABLE =
VAR vEmployee = SELECTEDVALUE(SlicerTable[Employee])
VAR vCurrent = SELECTEDVALUE(SlicerTable[Current])
VAR vActive = SELECTEDVALUE(SlicerTable[Active])

RETURN
IF (
    vEmployee = "Employee" && vCurrent = "Current" && vActive = "Active & TSOS",
    FILTER (
        TABLE,
        TABLE[DATE_ID] = 20250505
        && TABLE[EMPLOYEE_CURRENT_ACT_TSOS_NBR] = 1
    ),
    FILTER (
        TABLE,
        FALSE()  // fallback: returns empty table
    )
)

Please mark this post as solution if it helps you. Appreciate Kudos.

[ParsaJafarian]

The IF expression you've provided also has the same issue as SWITCH since they both only return scalar values, not tables. 

[ParsaJafarian]

I have tried this approach. However, I get an empty table when I add the selected values to the filter:

Otherwise when I don't add them, it works fine:

The employee variable is true:

So I conclude that boolean expressions that don't depend on the table that is being filtered are not considered at all. In this case, the vEmployee is a variable from a selected slicer rather than from a column in the table that's being filtered. 

[bhanu_gautam]

@ParsaJafarian , Try using

DAX
FILTERED_TABLE =
FILTER(
TABLE,
TABLE[DATE_ID] = 20250505 &&
TABLE[EMPLOYEE_CURRENT_ACT_TSOS_NBR] = 1 &&
vEmployee = "Employee" &&
vCurrent = "Current" &&
vActive = "Active & TSOS"
)