Forum Discussion

Blablabla's avatar
Blablabla
Advocate I
1 year 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
    1 year 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
Community Champion
1 year ago

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

  • Blablabla's avatar
    Blablabla
    Advocate I
    1 year 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
      1 year 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