Converting Case statement, Filter from t-SQL query to DAX

Question

I have a problem converting below t-sql query into DAX.

Overview - There are two sample tables - Table1 and Table2 with below schema

Table1 (ID varchar(20),Name varchar(30))

Table2 (CapID varchar(20),CAPName varchar(30), CapID_Final varchar(20))

Please note : There exists one to many relationship between above tables : [ID] in Table2 with [CapID] in Table1

I am trying to derive CapID_Final column in table2 based on conditions as per my t-SQL query in below which works perfectly fine -

SELECT CASE
       WHEN [CapID] like 'CA%' and [CAPName]='x12345-Sample' 
       and [CapID] not in(select [ID] from Table1 where Name='x12345-Sample')
       THEN 'Undefined_Cap_1'

       WHEN [CapID] like 'CA%' and [CAPName]='z12345-Sample' 
       and [CapID] not in(select [ID] from Table1 where Name='z12345-Sample')
       THEN 'Undefined_Cap_2'

       WHEN [CapID] like 'CA%' and [CAPName]='a123-Sample' 
       and [CapID] not in(select [ID] from Table1 where Name='a123-Sample')
       THEN 'Undefined'

       ELSE [CapID]

   END AS [CapID_Final] from Table2

However, I want the same derivation for CapID_Final column in Power BI in a calculated column using DAX.

So far, I have tried below code - but it returns "Undefined" for even matched conditions -

CapID_Final = 
IF(LEFT(Table2[CapID],2)="CA" && Table2[CAPName]="z12345-Sample" && 
NOT 
(COUNTROWS (
    FILTER (
        Table1,CONTAINS(Table1,Table1[ID],Table2[CapID])
    )
) > 0),"Undefined_Cap_1","Undefined"
)

I am not familiar with DAX, however I tried and couldn't figure it out. Could you please let me know how to convert my sql query to equivalent DAX in Power BI?

Answer 1

A SWITCH is basically the equivalent of a CASE clause here:

CapID_Final =
SWITCH (
    TRUE (),
    LEFT ( Table2[CapID], 2 ) = "CA"
        && Table2[CAPName] = "x12345-Sample"
        && NOT (
            Table2[CapID]
                IN CALCULATETABLE ( VALUES ( Table1[ID] ), Table1[Name] = "x12345-Sample" )
        ), "Undefined_Cap_1",
    LEFT ( Table2[CapID], 2 ) = "CA"
        && Table2[CAPName] = "z12345-Sample"
        && NOT (
            Table2[CapID]
                IN CALCULATETABLE ( VALUES ( Table1[ID] ), Table1[Name] = "z12345-Sample" )
        ), "Undefined_Cap_2",
    LEFT ( Table2[CapID], 2 ) = "CA"
        && Table2[CAPName] = "a12345-Sample"
        && NOT (
            Table2[CapID]
                IN CALCULATETABLE ( VALUES ( Table1[ID] ), Table1[Name] = "a12345-Sample" )
        ), "Undefined",
    Table1[CapID]
)

You might even be able to refactor it a bit to be more code efficient. Assuming I didn't make any logic mistakes:

CapID_Final =
VAR IDs =
    CALCULATETABLE ( VALUES ( Table1[ID] ), Table1[Name] = Table2[CAPName] )
RETURN
    IF (
        LEFT ( Table2[CapID], 2 ) = "CA"
            && NOT ( Table2[CapID] IN IDs ),
        SWITCH (
            Table2[CAPName],
            "x12345-Sample", "Undefined_Cap_1",
            "z12345-Sample", "Undefined_Cap_2",
            "a12345-Sample", "Undefined"
        ),
        Table1[CapID]
    )

Answer 2

As a best-practice never use calculated column. In fact, if extensively used they slow down your model refresh and heavily increase your model weight (because they are not compressed). Instead, calculate it in your back-end database or using M Query.
Having said this, the solution to your question is very simple using a SWITCH function:

SWITCH ( <Expression>, <Value>, <Result> [, <Value>, <Result> [, … ] ] [, <Else>] )

In your case would be as follow:

CapIDFinal:=
SWITCH(TRUE(),
AND(CONDITION_1_1, CONDITION_1_2), "Value if condition 1 is true",
AND(CONDITION_2_1, CONDITION_2_2), "Value if condition 2 is true",
"Value if none of above conditions is true
)