Is it possible to slice on date(year ,quarter,month,etc) in Direct Query mode?

[raghavkh]

Hello,

I just wanted to whether is it possible to drill down on date in direct query mode from SQL database? It works in the import mode but unfortunately, I would have to use direct query.

[waltheed]

Hi raghavkh,

You can slice with the filter controls for example, but you cannot create a hierarchy in Direct Query Mode, which is probably what you mean.

You can vote here for this additional functionality:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/10435572-create-dax-measures-fiel...

[raghavkh]

Thanks. By filter control do you mean slicers in visuals right?

[v-sihou-msft]

@raghavkh

As I tested in my local environment, we can create hierarchy in Direct Query mode with SQL Database. The slicers works properly as well. I use Power BI Desktop April Update(2.34.4372.322)

Regards,

[elliotdixon]

@raghavkh is that DimDate table just a connection from your SQL database or is it built using DAX - add blank query?
Just trying to work out how to add a date table.

Cheers.

[raghavkh]

Its a column in my table. I'm not using DAX but I think that would be my next step to create columns for year month , quarter etc for live connections.

[elliotdixon]

@raghavkh Ok Cheers
I think I have to take the next step and build a date table in the database as well.

[mbdtz · Accepted solution]

Hi @elliotdixon

I'm not sure if you're able to create a date table while using Direct Query but this post helped me with my Power BI date problems

http://community.powerbi.com/t5/Desktop/Possible-Tip-Trick-Dynamic-Date-Dimension-Table/td-p/8462

good luck 🙂

[elliotdixon]

Thanks @mbdtz but you can't create tables with Direct Query.

[waltheed]

Yes, that's what I mean.