cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Botturovic Frequent Visitor
Frequent Visitor

Overcome data type problem in direct query

I know that direct query mode has limitations in modeling colums and data in general, but maybe you can help me to overcome this limitations in a particular case.

This is the data type format i have

Posting Date.png

 

and i would like to work on a report analyzing the data aggregated by years and months, but i can't duplicate, split, transform the data column in any way because of the direct query mode. So the result is like this, data not aggregated at all.

report.png

Do you have any suggestion about how i can manage this problem?

 

thank you if someone can help me.

3 REPLIES 3
pawelpo Member
Member

Re: Overcome data type problem in direct query

You can try to add calculated columns even in DirectQuery mode. On the right side in the Fields panel right-click on the table name and choose New column. Then you can simply use DAX (with several limitations) to create all columns needed, e.g.:

 

Year = YEAR(YourTable[Posting Date])

Month Number = MONTH(YourTable[Posting Date])
Botturovic Frequent Visitor
Frequent Visitor

Re: Overcome data type problem in direct query

Thank you for yout help pawelpo,

 

i've tried to follow your instructions but i got an error message of the same type:

"this operation generates a query that is not supported in Direct Query mode."

 

v-yulgu-msft Super Contributor
Super Contributor

Re: Overcome data type problem in direct query

Hi @Botturovic,

 

In my test, I was able to create calculated columns to generate Year column and month column. And I was able to add these two calculated columns into Axis section of the bar chart.

Year = YEAR(test04[Date])
Month = Month(test04[Date])

 

1.PNG

 

Besides, please try the Bin function to see whether it works for you.

2.PNG3.PNG

 

Best regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 39 members 1,057 guests
Please welcome our newest community members: