Forum Discussion

Sharmi_28's avatar
Sharmi_28
Helper I
2 years ago

Help with SQL subquery to DAX Please

I have following SQL query. Could you please help me on how to check where clause in DAX. Thank

SELECT
       SUM (ColAmt)
FROM Teams T
WHERE
      (T.DateCol >= FisrtDateofPreviousMonth And T.DateCol <= LastDateofPreviousMonth)    And
      NOT Exists ( SELECT  1  FROM Teams P
                         Where
                               P.Col1 = T.Col1 And
                               P.DateCol >= FisrtDateofCurrentMonth And P.DateCol <= LastDateofCurrentMonth                      
                        )

Need Help

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    2 years ago

    Hi Sharmi_28 , In DAX instead of Where clause Filter function can be used, try below mentioned DAX meausure

     

    EVALUATE
    VAR FirstDateofPreviousMonth = EOMONTH(TODAY(), -2) + 1
    VAR LastDateofPreviousMonth = EOMONTH(TODAY(), -1)

    VAR FirstDateofCurrentMonth = EOMONTH(TODAY(), -1) + 1
    VAR LastDateofCurrentMonth = EOMONTH(TODAY())

    RETURN
    FILTER(
    VALUES(T[Col1], T[Col2], T[Col3], T[DateCol]),
    T[DateCol] >= FirstDateofPreviousMonth
    && T[DateCol] <= LastDateofPreviousMonth
    && NOT (
    CALCULATETABLE(
    VALUES(P[Col1]),
    P[Col1] = T[Col1],
    P[DateCol] >= FirstDateofCurrentMonth
    && P[DateCol] <= LastDateofCurrentMonth
    )
    )
    )

     

    Please accept as solution if it helps

    • Sharmi_28's avatar
      Sharmi_28
      Helper I
      2 years ago

      Thanks for reply and solution.
      I tried to apply your DAX but its not working for me.

      I trying something like

      Measure =
      VAR FirstDateofPreviousMonth = EOMONTH(TODAY(), -2) + 1
      VAR LastDateofPreviousMonth = EOMONTH(TODAY(), -1)

      VAR FirstDateofCurrentMonth = min(DimCalendar[Date])
      VAR LastDateofCurrentMonth = max(DimCalendar[Date])

      RETURN
      CALCULATE(
      Sum(FactTable[Amount]),
      FactTable[Date] >= FirstDateofPreviousMonth &&
      FactTable[Date] <= LastDateofPreviousMonth
      &&
      NOT ( FactTable[Code]= FactTable[Code] &&
      FactTable[Date] >= FirstDateofCurrentMonth && FactTable[Date] <= LastDateofCurrentMonth
      )
      )

      But its giving error.