Translate SQL query with complex CASE to DAX

[Blablabla]

Hello,

I have this query where FactPXD is 1 to many to Dept and the filter direction is single from the Dept to FactPXD, how can I create this measure in DAX :

SELECT

    SUM(

        CASE

            WHEN D.DeptName IN ('A','T')  OR

                                           (D.DeptTpe='B'

             AND F.QTY < 0) THEN F.QTY                                                                                

            ELSE 0

        END

    ) AS Total

FROM

    FactPXD AS F

    LEFT JOIN Dept AS D ON D.[Dept Id] = F.[DepId]

WHERE

    D.[Dept Group] LIKE 'T%'

    AND   D.[Dept Group] NOT LIKE 'TC%'

[AmiraBedh · Accepted solution]

Try the following :

Your measure =
CALCULATE (
    SUMX (
        FactPXD,
        SWITCH (
            TRUE(),
            RELATED(Dept[DeptName]) IN {"A", "T"}, FactPXD[QTY],
            RELATED(Dept[DeptTpe]) = "B" && FactPXD[QTY] < 0, FactPXD[QTY],
            0
        )
    ),
    FILTER (
        Dept,
        LEFT(Dept[Dept Group], 1) = "T" &&
        LEFT(Dept[Dept Group], 2) <> "TC"
    )
)

[Greg_Deckler]

@Blablabla Use SWITCH( TRUE(), ... ) for the CASE statement. Probably SUMX for summing.

[Blablabla]

@Greg_Deckler  I tried the following but it doesn't recognise the columns from my dimensions :

TotalMeasure :=
CALCULATE (
    SUMX (
        FactPXD,
        SWITCH (
            TRUE(),
            Dept[DeptName] IN {"A", "T"}, FactPXD[QTY],
            Dept[DeptType] = "B" && FactPXD[QTY] < 0, FactPXD[QTY],
            0
        )
    ),
    FILTER (
        Dept,
        LEFT(Dept[Dept Group], 1) = "T" &&
        LEFT(Dept[Dept Group], 2) <> "TC"
    )
)

[muhammad_786_1]

TotalQty =

CALCULATE(

    SUMX(

        FactPXD,

        IF(

            RELATED(Dept[DeptName]) IN {"A", "T"} ||

            (RELATED(Dept[DeptTpe]) = "B" && FactPXD[QTY] < 0),

            FactPXD[QTY],

            0

        )

    ),

    FILTER(

        Dept,

        CONTAINSSTRING(Dept[DeptGroup], "T") &&

        NOT(CONTAINSSTRING(Dept[DeptGroup], "TC"))

    )

)

Filtering: The FILTER function precisely filters DeptGroup to start with "T" but not "TC", using CONTAINSSTRING to ensure accurate filtering as per our requirement.

These are some sample queries for verify data in SQLMS.

https://drive.google.com/file/d/1UE53tW5qDi5U0LoH5vbuLBT1MouweJHw/view?usp=sharing