topic Re: DAX help - Less than or Blank in DAX Commands and Tips

DAX help - Less than or Blank

Anonymous — Mon, 04 Apr 2022 07:33:07 GMT

Hi geniuss!!!

I'm trying to create a measure that I'll be using against a calendar table to plot number of active contacts at any given time, but I'm tearing my hair our with some of these measures...

In simple terms, I'm trying to create a measure, that will count the number of our members, at any given time, who have a qualification date of greater than or qual to *Date*

AND a leave date that is blank OR less than or equal to *Date*.

Its the 2nd part that I'm having trouble with. So far I've got to:

COUNT('CE vwContact (2)'[Rics_contactno], 'CE vwContact (2)'[Rics_LapsedDate]<= 'BI vwCalendar (2)','-Calendar'[Date],

OR('CE vwContact (2)'[Rics_LapsedDate]= BLANK())

But its saying its incorrect. I think it could be because I'm trying to do 2 formulas at the same time, but I've tried to seperate them with no luck either....

My whole formula is:

Members TEST =

CALCULATE(

DISTINCTCOUNT('CE vwContact (2)'[Rics_contactno]),

'CE vwContact (2)'[Rics_ElectionDate]>= 'BI vwCalendar (2)','-Calendar'[Date],

COUNT('CE vwContact (2)'[Rics_contactno], 'CE vwContact (2)'[Rics_LapsedDate]<= 'BI vwCalendar (2)','-Calendar'[Date],

'CE vwContact (2)', 'CE vwContact (2)'[Rics_LapsedDate]= BLANK()

))

But its just the counting of the blank or less than date.

Can anyone help?

Cheers

Re: DAX help - Less than or Blank

tamerj1 — Mon, 04 Apr 2022 07:50:00 GMT

Hi Anonymous
You may try

Members TEST = VAR CurrentDate = MAX ( '-Calendar'[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( 'CE vwContact (2)'[Rics_contactno] ), 'CE vwContact (2)'[Rics_ElectionDate] >= CurrentDate, OR ( 'CE vwContact (2)'[Rics_LapsedDate] <= CurrentDate, 'CE vwContact (2)'[Rics_LapsedDate] = BLANK () ) )

Re: DAX help - Less than or Blank

Anonymous — Mon, 04 Apr 2022 08:01:50 GMT

Awesome! That works, I'm going to have to play around with getting the visualisation of active members by date but the measure works, I just had to change the Or to an AND, but its accepted in which is great.

Thank you!!