How to Solve A table of multiple values was applied where a single value was expected

[Anonymous]

Hello All,

In my report i have three tables, and their relashionship looks as below image.

Now here Date table is master table and it is connected with Date of DateRange table and BEGIN_DATE of May table.

Sample Data:- 

Date Table:-

Date
01-04-2018
02-04-2018
03-04-2018
04-04-2018
05-04-2018
06-04-2018
07-04-2018
08-04-2018
09-04-2018
10-04-2018
11-04-2018
12-04-2018

DateRange:-

TimeRange

05:00:00

05:30:00

06:00:00

06:30:00

07:00:00

07:30:00

08:00:00

08:30:00

09:00:00

09:30:00

10:00:00

10:30:00

May Table:-

TIME	EMP_ID	BEGIN_DATE
6:52:00 AM	00129235	01-04-2018
5:37:00 PM	00129235	02-04-2018
1:59:00 PM	00129235	03-04-2018
8:55:00 PM	00129235	04-04-2018
1:48:00 PM	00129235	05-04-2018
8:28:00 PM	00129235	06-04-2018
2:20:00 PM	00129235	07-04-2018
8:39:00 PM	00129235	08-04-2018

Now, Im using Date column from Date table as 1st slicer to select date,

TimeRange column from DateRange table as 2nd slicer to select the time range.

I have written below 2 calculated columns in DateRange table.

SelectedDate-Time = 
VAR maxDate = CALCULATE(MAX('Date'[Date]),ALLEXCEPT('Date','Date'[Date]))
VAR maxTime = CALCULATE(MAX('DateRange'[TimeRange]),ALLEXCEPT('DateRange','DateRange'[Date],DateRange[TimeRange]))
RETURN
 maxDate&" "&maxTime

NextDate = ((DateRange[SelectedDate-Time]+1)-TIME(0,0,1))

 Now i would like to compare the Date-Time column values from May table to these both columns.

So for that, i have below Calculated column in May Table.

I.e

Date-Time3 = 
    IF(May[Date-Time] >=VALUES(DateRange[SelectedDate-Time]) && May[Date-Time] <= VALUES(DateRange[NextDate]),
        May[Date-Time],BLANK())

But here im getting error as

A table of multiple values was applied where a single value was expected.

How can i solve this.

Please suggest me.

Thanks

Mohan V

[v-yuezhe-msft · Accepted solution]

@Anonymous,

It seems that you are creating measures(SelectedDate-Time ,NextDate,Date-Time3) instead of calculated columns. If so, please change your DAX to the following. However, if you still get errors, please share sample data of Date column in DateRange table with the TimeRange column.

Date-Time3 = 
    IF(MAX(May[Date-Time]) >=DateRange[SelectedDate-Time] && MAX(May[Date-Time] )<= DateRange[NextDate],
        MAX(May[Date-Time]),BLANK())

Regards,
Lydia