Using a measure returning a date inside an IF function

[67nmark]

Hello,

I have created a measure:

Date marker = VALUES(tblVisit[MaxDate])

The measure produces the correct result.

I want to use the measure inside an IF function in a new column (in a different table), something like:

IF(tbl[Date]>[Date marker],"y","n")

This gives me an error: 'A table of multiple values was supplied where a single value was selected'.

If I hard code a date everything works fine:

IF(tbl[Date]>DATE(2018,04,01),"y","n")

Is anyone able to let me know why using the measure does not work?

Cheers,

Mark

[Anonymous]

From what I can tell, Date Marker is being used to return a list of dates.  When you enter your IF statement, you are checking if tbl[DATE] is greater than your list of dates.  Since you haven't provided a method of aggregation of that list, Power BI is unsure what you are trying to do.

The points I would raise here are:

• How is [Date Marker] any different in logic to simply using tblVisit[MaxDate] inside a formula?  Is the date marker measure doing what you intended it to do?
• What logic is your IF statement supposed to be?  Are you trying to get a list of dates and you want tbl[Date] to be greater than all of the dates aggregated together?
• Reading your various measures, they are written more like custom columns.  I don't think this is what you intended.

[67nmark]

Hello,

Thanks for the reply. I have sorted the issue out - my problem was driven by a number of issues, the largest of which was that my date measure was formatted as text!!

Thanks again.

Cheers,

Mark

[67nmark · Accepted solution]

Hello,

Thanks for the reply. I have sorted the issue out - my problem was driven by a number of issues, the largest of which was that my date measure was formatted as text!!

Thanks again.

Cheers,

Mark