Forum Discussion

Blablabla's avatar
Blablabla
Advocate I
2 years ago
Solved

Translate SQL query with complex CASE to DAX

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 ...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

    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"
    )
)
Blablabla's avatar
Blablabla
Advocate I
2 years ago

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's avatar
muhammad_786_1
Solution Supplier
2 years ago
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