Forum Discussion

Blablabla's avatar
Blablabla
Icon for Advocate I rankAdvocate 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"
    )
)
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
2 years ago

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

Blablabla's avatar
Blablabla
Icon for Advocate I rankAdvocate 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
    Icon for Solution Supplier rankSolution 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