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.Or...
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]))