DAX issue with date filters

[DHB]

DueTimeTable

I’m trying to assign a DueTimeBucket value to a list of times using the table I created above.

To assign a bucket to each time I run this DAX…

DueTimeBucket = CALCULATE(VALUES(DueTimeBucket[DueTimeBucket]),FILTER(DueTimeBucket,U_DIM_LM_QUIZ[ACDTDueTime]>=DueTimeBucket[Min] && U_DIM_LM_QUIZ[ACDTDueTime]<DueTimeBucket[Max]))

But get this error message….

“A table of multiple values was supplied where a single value was expected.”

The ACDTDueTime column has a time data type, as has the Min and Max columns in the DueTimeBucket table.

Can anyone see where I might be going wrong?

Thank you in advance.

DHB

[DHB]

I've made some progress here.  By changing the ACDTDueTime column to HH:mm:ss I've managed to get all the times into a DueTimeBucket except for the ones beteween 23:00:00 and 00:00:00 which are left blank.

I can't see why PBI doersn't like these particular ones.

[DHB · Accepted solution]

A bit more progress.  I set the max on the 23 bucket to 23:59:59.5 and now all the times are allocated.  It seemed not to like the circular nature of the times and liked to have a clear start and end to the day, not the start and end being the same.

[v-huizhn-msft]

Hi @DHB,

Congadulations for you have resolve your issue. Please marks the correspoding reply as answer, so other people will find the solution clearly. Thank you for understanding and cooperation.

Best Regards,
Angelia

[Baskar]

Hi,

1. Can u please tell me did you create calculated column or Mesure ?

2. Do u ahve relationship with 2 Tables ?

[DHB]

 Hi Baskar,

thank you for your response.  it came through just as I was writing my last post.  I seem to have found the sulution.

DHB

[Baskar]

Nothing Problem in DAX. 

Note:

The Problem is your value supply multiple columns.

U ahve to check the relationship between two tables , which relationship it have . 

1 to 1

or

1 to many

if 1 to many that is the problem.