Forum Discussion
DAX Calculation with multiple rules
Hi there,
I am looking create a measure "Quote Waiting Customer Review" with multiple rules.
The rules are below.
CANCELLED is a date field.
INVOICED is a date field.
LGI_MAINTENANCE is a date field.
SENT_TO_MIN is a date field.
COMPLETED is a date field.
QUOTED is a date field.
STATUS_DATE is a date linked to calender. I need to call this Var STATUS_DATE = SELECTEDVALUE(Calendar[Date]) in the DAX
COUNT where (CANCELLED is null or CANCELLED >= :STATUS_DATE)
and (INVOICED is null or INVOICED >= :STATUS_DATE)
and (LGI_MAINTENANCE is null or LGI_MAINTENANCE >= :STATUS_DATE)
and (SENT_TO_MIN is null or SENT_TO_MIN >= :STATUS_DATE)
and (COMPLETED is null or COMPLETED >= :STATUS_DATE)
and (ACCEPTED is null or ACCEPTED >= :STATUS_DATE)
and (QUOTED < :STATUS_DATE)
Any help would be appreciated.
Anonymous did you copy and paste my code into your PBIX and just change the table name from 'Table' to whatever your table is called?
12 Replies
- TheoC
Community Champion
Hi Anonymous
You can use the SWITCH function to achieve this.
New Measure = VAR _StatusDate = SELECTEDVALUE ( 'Calendar'[Date] ) RETURN SWITCH ( TRUE () , 'Table'[CANCELLED] = "" || 'Table'[CANCELLED] >= _StatusDate , 1 , 'Table'[INVOICED] = "" || 'Table'[INVOICED] >= _StatusDate , 1 , 'Table'[LGI_MAINTENANCE] = "" || 'Table'[LGI_MAINTENANCE] >= _StatusDate , 1 , 'Table'[SENT_TO_MIN] = "" || 'Table'[SENT_TO_MIN] >= _StatusDate , 1 , 'Table'[COMPLETED] = "" || 'Table'[COMPLETED] >= _StatusDate , 1 , 'Table'[ACCEPTED] = "" || 'Table'[ACCEPTED] >= _StatusDate , 1 , 'Table'[QUOTED] < _StatusDate , 1 , 0 )Using the above, you can then COUNTROWS / FILTER = 1 on the above.
Hope this helps 🙂Theo
- TheoC
Anonymous did you copy and paste my code into your PBIX and just change the table name from 'Table' to whatever your table is called?