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

[chrisgreenslade]

I need to wait for the data to refresh, which is over 6.5M rows.

I need to step away and come back to this tomorrow.

Thanks Melissa. I will be in contact again, no doubt.

[chrisgreenslade]

Hi,

I’ve managed to remove the non printable characters at the end of the name for [Terminal ID]. However, I am now getting an error which states that

The field ‘Purchase Start Date & Time’ of the record wasn’t found.

This particular column is a calculated column.

Any ideas?

[Melissa]

Can you translate that logic to M?

If not what is the DAX syntax for that CC?

[chrisgreenslade]

Here is a link to the full PBIX.

https://drive.google.com/file/d/1yb_YXG_nRrm-1964OGJPqr_ACpcg0u4O/view?usp=sharing

[chrisgreenslade]

@Melissa,

Ideally, I don’t even need Purchase Duration in the P&D Table, as I currently have something similar in Tariffs[Tariffs Duration]. What I need is for the blank entries of the Purchase End Date and Time to be populated based on the data.

Chris

[chrisgreenslade]

I assumed that my DAX for a calculated column which works for P&D Purchases[Purchase Tariff Name], could just be substituted with ‘Tariffs’[Tariff Duration] as below. But it doesn’t work.

Purchase Tariff Name = CALCULATE(
VALUES( ‘Tariffs’[Tariff Duration] ) ,
FILTER( Tariffs ,
‘P&D Purchases’[Terminal ID] = ‘Tariffs’[Terminal ID ] &&
‘P&D Purchases’[Purchase Start Day Name] = ‘Tariffs’[Day Name] &&
‘P&D Purchases’[Purchase Start Date & Time] >= ‘Tariffs’[Tariff Start Date] &&
‘P&D Purchases’[Purchase Start Date & Time] <= ‘Tariffs’[Tariff End Date] &&
‘P&D Purchases’[Purchase Amount] >= ‘Tariffs’[Tariff Amount Start] &&
‘P&D Purchases’[Purchase Amount] <= ‘Tariffs’[Tariff Amount End] &&
( ( ‘P&D Purchases’[Purchase Start Time] >= ‘Tariffs’[Tariff Start Time 1] * (1/24) &&
‘P&D Purchases’[Purchase Start Time] < ‘Tariffs’[Tariff End Time 1] * (1/24) ) ||
( ‘P&D Purchases’[Purchase Start Time] >= ‘Tariffs’[Tariff Start Time 2] * (1/24) &&
‘P&D Purchases’[Purchase Start Time] < ‘Tariffs’[Tariff End Time 2] * (1/24) ) ) ) )

[Melissa]

Hi @chrisgreenslade,

No that doesn’t work and here’s why. If you lookup this record in P&D Purchases

.
You’ll find that it matches these 2 records in your Tariffs

So the result is 1 distinct Tariff Name but 2 distinct Tariff Durations…
This overlap in date range is something you should resolve in your Tariffs dimension table.

Let me know how you want to handle this.

[chrisgreenslade]

I think the Tariff End Date for M-HARD-1 in the first row should read 04-04-19, so that the tariff is unique. The Tariff duration is also incorrect and should be 10 hrs (not 13).

[chrisgreenslade]

Does the DAX now calculate for Purchase Duration? The ultimate goal is to have a visual that shows cars entering and leaving a car park over the course of a day, week, etc.

Currently our data is nasty from a particular source, which means we don’t get a purchase start and end date. Otherwise, this would have been an easy exercise.

I need to fill in the blanks from the company that can’t provide the above, but also analyse car park occupancy throughout the day.

It’s a nightmare.

[Melissa]

Hi @chrisgreenslade,

@chrisgreenslade

I think the Tariff End Date for M-HARD-1 in the first row should read 04-04-19, so that the tariff is unique. The Tariff duration is also incorrect and should be 10 hrs (not 13).

I do agree with you there…

Hopefully you understand that before you move on, you need resolve all these types of issues in your data. Otherwise how can you expect a valid outcome when your dimension table contains records that are ‘wrong’ - no amount of M or DAX code can account for that.

Furthermore I’d suggest to close this thread, now. Feel free to open a new topic once all issues with your data have been resolved.

Thank you.