DAX Equations with DirectQuery

[dylanndengu]

Hi Everyone,

I am using DirectQuery to access data in a PostgreSQL database with millions of rows. I'm accessing about 10 different tables and trying to make reports using them. One of them is current week vs previous week revenue. However, I am having some trouble using the DAX calculations to form weeks.

As seen below, the "reportdate" I would like to access is in the "public_fact_sales_detail" table. However, Power BI is telling me it cannot find the table, but it is the home table shown in the circled area below. Why are the DAX not recognizing any of my tables in DirectQuery?

Kind regards

Dylan

[v-xulin-mstf]

Hi @dylanndengu,

Here is the modeling limitation of DQ.

Although import mode has complete modeling capabilities, it is slow to import data.
You can model in SQL SERVER Analysis Services.
Then Connect to Analysis Services tabular data with Live Connection in Power BI Desktop.

You can refer:

https://docs.microsoft.com/en-us/analysis-services/tutorial-tabular-1400/as-lesson-1-create-a-new-ta...

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-analysis-services-tabular-data

Best Regards,

Link

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

[amitchandak]

@dylanndengu , I see a space above and I see a _ below after public

Also not all column operation are supported on direct query 

[dylanndengu]

Hi again @amitchandak, noted the change but I still can't access the table for DAX calculations. Alternatively, would you have any suggestion as to how I could import the data into Power BI quickly? Because it takes at least 2 hours or so to import all the data from PostgreSQL. Even after it is imported, it then can take an hour to refresh the next day.