Date Table in Direct Query mode


#1

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:


#2

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)


#3

Thanks Sam

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

Cheers


#4

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


#5

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.