Date Table in Direct Query mode

Hi

So I have run into another issue when working in direct query mode (which I need to do due to the way my client is embedding their Power BI in D365 as otherwise there will be huge data load issues - I think?!)… I am unable to create a date table in the way that I have been doing after following the tutorials on this site

Does anyone have a workaround for this so that I can still develop some deep dive insights using dates? I currently can’t see any date hierarchies and so can’t produce meaningful insights by period / quarter / month / annual etc etc - which is pretty fundamental to what is needed !!

PLEASE HELP :frowning:

Yes, another big issue I know.

The client must have a date table for you to connect to??

I mean if they want to use direct query they must have databases set up and also have custom date table you can connect to in the same way. Every business I’ve ever worked with with databases already has date tables I could connect to. (I didn’t always do so, but they were there)

1 Like

Thanks Sam

Hadn’t even considered that as had just begun working in my traditional manner - that would make sense!!

Cheers

Sam,

thanks for the response to my previous question. I am quickly learning that using Direct Query creates more and more headaches when you are used to designing in import mode and keep hitting little stumbling blocks

One that I have been unable to solve is the lack of Enter Data option. I want to use a SWITCH statement to enable users to flick between 4 or 5 metrics to make a full report page dynamic (in the way you showcase in the Regional Performance report) but without the ability to enter data and create a simple table I am stuck!

One possible solution is to ask the solution architect to add a new table into the source itself but that means having to spec up, pay for and wait for that development work to happen rather than simply having a simple 2 minute exercise to do in Power BI

Any suggestions for a workaround?

Dave

Yes it is very limiting.

Honestly there just isn’t a workaround that I’m currently aware of other that the one you describe.

DirectQuery just has so many limitations, that’s all I can really say. Hopefully it will improve but it’s likely there’s a few reasons why it is like it is.

I have just been through this scenario again today, and it seems like the dates table from direct query still does not automatically create heirachies. There is a bit of writing online that says you could manually generate a date heirachy by dragging and dropping, but this still does not quite look / behave the same

Here is the M driven one

Here is the direct query one

Is it possible to make the hierarchies work properly?
I also found marking as a date table blows away the heirachy.

@Dave
Hi,
I was able to create Custom Calendar in Power Query M while using Direct Query Mode :slight_smile: