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

            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
    Icon for Super User rankSuper 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
      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