Forum Discussion
DAX help - Less than or Blank
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
Hi Anonymous
You may tryMembers 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 () ) )
2 Replies
Hi Anonymous
You may tryMembers 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 () ) )