DAX mesaure with multiple Date filters

[Phoenix_Bird]

I'm trying to create a measure to filter some data by multiple date fields and values.

I've managed to create a measure to get the No Required where the Date Approved is not 1st August 2022 and is not blank:

Measure =

CALCULATE([No Required],

Table[Date Approved].[Date] <> DATE(2022,08,01)

&& Table[Date Approved].[Date] <> BLANK())

However, I also need to add 2 more conditions - where the Date Offer Accepted = Blank && the Date Offer Accepted  = 2022,08,01

I've tried this but I'm getting the error below:

Measure =

CALCULATE([No Required],

Table[Date Approved].[Date] <> DATE(2022,08,01)

&& Table[Date Approved].[Date] <> BLANK())

&& Table[Date Offer Accepted].[Date] = BLANK()

&& Table[Date Offer Accepted].[Date] = DATE(2022,8,01)

Error: A single value for variaton 'Date' for column 'Date Offer Accepted' in table 'Table' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[Phoenix_Bird]

Perfect thanks for your help

[jdbuchanan71 · Accepted solution]

Try it like this.

Your Measure =
CALCULATE (
    [No Required],
	Table[Date Approved] <> DATE(2022,08,01) &&
	Table[Date Approved] <> BLANK()) &&
	(
		Table[Date Offer Accepted] <> DATE ( 2022, 08, 01 ) ||
		Table[Date Offer Accepted]] <> BLANK ()
	)
)

[Phoenix_Bird]

OK thanks - I needed || instead of &&

I've now managed to get to the correct number by doing 2 separate measures, and referencing the first measure in the second:

Measure 1:

Table[Date Approved] <> DATE(2022,08,01)

&& Table[Date Approved] <> BLANK())

Measure 2:

CALCULATE([Measure 1],

Duplicate[Date Offer Accepted] = DATE(2022,08,01)

|| Duplicate[Date Offer Accepted] = BLANK())

Is there a way of getting this into 1 measure rather than having 2?

When I try the below, it returns the wrong numbers:

CALCULATE([No Required],

Table[Date Offer Accepted] = DATE(2022,08,01)

|| Table[Date Offer Accepted] = BLANK()

|| Table[Date Approved] <> DATE(2022,08,01)

|| Table[Date Approved] <> BLANK())

[jdbuchanan71]

That's becuase these two lines will not let any rows come through.

&& Table[Date Offer Accepted] = BLANK()
&& Table[Date Offer Accepted] = DATE(2022,8,1))

No line will have [Date Offer Acceted] = BLANK() AND [Date Offer Acceted] = 2020-8-1

[Phoenix_Bird]

Thanks for your reply. I tried that measure you suggested but it doesnt return any results.

I do have a DimDate table in my model but i deleted the relationship to try and understand it better. I can recreate the relationship to go from 

Table [Date Approvd] = DimDate [Date]

[jdbuchanan71]

@Phoenix_Bird 

It looks like you have the closing ) of your CALCULATE in the wrong place with the new filters.

Also, if you are referencing a date filed like Table[Date Approved], there is no need to use Table[Date Approved].[Date].  The .[Date] is only there becaue you don't have a calendar table in your model and you really should have one.  Try the measure like this and see if it works.

Measure =
CALCULATE([No Required],
Table[Date Approved] <> DATE(2022,8,01)
&& Table[Date Approved] <> BLANK()
&& Table[Date Offer Accepted] = BLANK()
&& Table[Date Offer Accepted] = DATE(2022,8,1))