Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

CASE STATEMENT for Measures

Can I get the equivalent DAX statement for the following mesures.

 

Measure1: 

COUNT( CASE WHEN
TABLE1.Submit_Date >= TABLE1.StartDate AND
TABLE1.OrderType IN ('Sweaters','Rugs')
THEN TABLE1.Order_Id END)

 

Measure2:

CASE WHEN TABLE2.StartDate >= TABLE1.StartDate AND (Table2.ComDate <= TABLE1.StartDate OR TABLE1.StartDate S NULL) THEN 1 ELSE 0 END

 

Measure3:

COUNT(CASE WHEN TABLE2.StartDate >= TABLE1.StartDate AND (TABLE2.StartDate <= TABLE1.StartDate OR TABLE1.StartDate IS NULL) THEN TABLE2.Id END)

3 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    let's try (but it could has an error depends on your data model):

    Measure1 = 
COUNTAX('Table1','Table1'[Submit_Date] >= 'Table1'[StartDate] AND
'Table1'[OrderType] IN {"Sweaters","Rugs"})

     

    Measure2 = IF('TABLE2'[StartDate] >= 'TABLE1'[StartDate] && ('TABLE2'[ComDate] <= 'TABLE1'[StartDate] || ISBLANK('TABLE1'[StartDate])), 1, 0)

     

    Measure3 = COUNTAX('TABLE2','TABLE2'[StartDate] >= 'TABLE1'[StartDate] && ('TABLE2'[ComDate] <= 'TABLE1'[StartDate] || ISBLANK('TABLE1'[StartDate])) )

     

    do not hesitate to give a kudo to useful posts and mark solutions as solution

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      For Measure1:

      I am able to get the following work 

       

      Measure 1:=CALCULATE(COUNTA(TABLE1[OrderId]), TABLE1[OrderType] IN {"Sweaters","Rugs"})

       

      Measure1:=COUNTAX(FILTER(TABLE1,TABLE1[SubmitDate]>=TABLE1[StartDate]),TABLE1[OrderId])

       

      I am unable to combine using the logic u have given.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        This Syntax now does not throw errors

        Measure 1:=CALCULATE(COUNTA(TABLE1[OrderId]), TABLE1[OrderType] IN {"Sweaters","Rugs"},FILTER(TABLE1,TABLE1[SubmitDate]>=TABLE1[StartDate]))