DAX Calculation with multiple rules

[jz5147]

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.

[jz5147]

It has not been solved. Sorry i was meant to click reply and instead clicked accept as solution. Is there any way to reopen?

[TheoC]

Hi @jz5147 

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

[jz5147]

Hi Theo,

Thanks for your reply.

'Table'[Cancelled] 'Table'[Invoiced] etc, are all dates in the same table.

How when i write the DAX i cant seem to select or find the these fields?

Thanks

[TheoC · Accepted solution]

@jz5147 did you copy and paste my code into your PBIX and just change the table name from 'Table' to whatever your table is called?

[jz5147]

Yeah I did

[TheoC]

@jz5147 can you screen shot what the measure you used?

[TheoC]

@jz5147 I am not sure how to reopen a post, but I promise I will work with you until it's resolved 🙂

[jz5147]

Thanks Theo! 

Please see below

[TheoC]

@jz5147 you need to change the all of the 'Table' to whatever the table name is called. For example, if the 'Table' is "AR_MANAGER BI_DASHBOARD_SRC_VW" then you need to replace the 'table' with "AR_MANAGER BI_DASHBOARD_SRC_VW". Try the below:

New Measure =

VAR _StatusDate = SELECTEDVALUE ( 'Calendar'[Date] )

RETURN 

SWITCH (
	TRUE () ,
		'AR_MANAGER BI_DASHBOARD_SRC_VW'[CANCELLED] = "" || 'AR_MANAGER BI_DASHBOARD_SRC_VW'[CANCELLED] >= _StatusDate , 1 ,
		'AR_MANAGER BI_DASHBOARD_SRC_VW'[INVOICED] = "" || 'AR_MANAGER BI_DASHBOARD_SRC_VW'[INVOICED] >= _StatusDate , 1 ,
		'AR_MANAGER BI_DASHBOARD_SRC_VW'[LGI_MAINTENANCE] = "" || 'AR_MANAGER BI_DASHBOARD_SRC_VW'[LGI_MAINTENANCE] >= _StatusDate , 1 ,
		'AR_MANAGER BI_DASHBOARD_SRC_VW'[SENT_TO_MIN] = "" || 'AR_MANAGER BI_DASHBOARD_SRC_VW'[SENT_TO_MIN] >= _StatusDate , 1 ,
		'AR_MANAGER BI_DASHBOARD_SRC_VW'[COMPLETED] = "" || 'AR_MANAGER BI_DASHBOARD_SRC_VW'[COMPLETED] >= _StatusDate , 1 ,
		'AR_MANAGER BI_DASHBOARD_SRC_VW'[ACCEPTED] = "" || 'AR_MANAGER BI_DASHBOARD_SRC_VW'[ACCEPTED] >= _StatusDate , 1 ,
		'AR_MANAGER BI_DASHBOARD_SRC_VW'[QUOTED] < _StatusDate , 1 , 
		0 )

[jz5147]

Thanks Theo

I understand that but even when i do this, the field [CANCELLED] ETC is greyed out and cant be select. I have checked that those fields are in the same table as where i am creating the meausre. Its actually not picking up the Field when typing the DAX

[TheoC]

@jz5147 my apologies, the screenshot has helped my understanding of the problem.  

If you were to delete the measure, then re-write it from scratch (instead of copying and pasting), are you able to see columns / fields after the Table name?  For example, if you type 'AR_MANAGER BI_DASHBOARD_SRC_VW' does it give you any columns/fields after it as options to select?

[jz5147]

Hi Theo

I have also tried this, not for the same metric above and not as complex.

STRIKE RATE = VAR _StatusDate = SELECTEDVALUE ( 'Calendar'[Date] )

RETURN

CALCULATE(COUNT('AR_MANAGER BI_DASHBOARD_SRC_VW'[ACCEPTED]),FILTER('AR_MANAGER BI_DASHBOARD_SRC_VW',

('AR_MANAGER BI_DASHBOARD_SRC_VW'[ACCEPTED] >= _StatusDate) && ( 'AR_MANAGER BI_DASHBOARD_SRC_VW'[CANCELLED] >= _StatusDate || 'AR_MANAGER BI_DASHBOARD_SRC_VW'[QUOTED] <> "null" )

))

I get this error