Directquery table limitations

[Gav]

Greetings, PowerBI simpleton here ! I have a fairly straightword calculation to make but because the main table uses directquery this is turning into a challenge !

Rather than get bogged down with my implementation detail I've tried to simplify what I'm trying to do in the following description.

So my main table is a live stream of timestamped 'events', accessed using directquery, and I need to figure how many staff were around when each event occurs. My intention was to create a calculated column to add the staff number to the main table.

The staff numbers are within a separate 'operations' table, covering 24 hours, which identifies ranges in hours and staff number. This operations table is like this :-

start time	end time	staff
00:00	06:00	1
06:00	07:00	2
07:00	10:00	3

So for each event in order to find the correct staff number I was simply going to use HOUR(timestamp), filter the operations table for rows with time of less than HOUR(timestamp) and use the row with the max time to the staff number . . .

but I can't do that because attempts to use MAXX fail because of directquery 😞

Any suggestions very much appreciated,

Gav

[v-robertq-msft · Accepted solution]

Hi, @Gav 

According to your requirement, you want to create a calculated column for a table whose connection mode is direct query.

However, direct query has great restrictions on calculated columns. For performance reasons, we do not recommend that you add calculated columns for direct query tables.

In order to add the staff number to the main table, I think you can try to achieve this in the original database (using stored procedure or ETL tools).

For more information about direct query, you can follow this link:

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.