DAX formula to summarize a DirectQuery Table

[kjani]

I have connected to a large fact table in Amazon Redshift via DirectQuery. This table has details for each day. The query runs very slow. So, I want to summarize the table by month and build an aggregate table that can be imported locally. I am very new to DAX and don't know how to summarize day column (yyyymmdd format) to month column (yyyymm) and build a new summarized fact table. Please help. Thanks

[v-zhenbw-msft · Accepted solution]

Hi @kjani ,

We can use the following steps to meet your requirement. First you need to transform Direct Query to Import.

1. Add a yyyymm column in table using Power Query Editor.

Number.ToText(Date.Year([Date]))
&"-"&
Number.ToText(Date.Month([Date]))

2. Then group by Custom column and the result like this,

If you have any question, please kindly ask here and we will try to resolve it.

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[v-zhenbw-msft]

Hi @kjani ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution.

Best regards,

Community Support Team _ zhenbw

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

[parry2k]

@kjani can you summarize at redshift and use that instead of summarizing in power bi, if you summarize in power bi, it is going to upload all the data and then perform summarization, why not do it at the source.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[kjani]

I don't have that option. 

[parry2k]

@kjani I don't know how then you are going to take advantage of this summarize table, although I'm not sure if query folding for Redshift data source happens, if it does then you can use group by in power query to summarize your data, you can test that and see if it improve the performance.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡