Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • TheoC's avatar
    TheoC
    4 years ago

    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?

TheoC's avatar
TheoC
Community Champion
4 years ago

Anonymous can you screen shot what the measure you used?

TheoC's avatar
TheoC
Community Champion
4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks Theo! 

    Please see below

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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

       

    • TheoC's avatar
      TheoC
      Community Champion
      4 years ago

      Anonymous 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 )

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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