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

            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%'

 

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"
    )
)

4 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Super User
    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