DAX formula are not working under the direct query (7-8 crore rows in the table)

[pranjalagarwal1]

Hello Everyone,

I have written the query for the following pointers raising an issue that I'm am not able to resolve:

• I have more than 7 crore records in the table (fetching data from a SQL query) using DirectQuery mode.

• The initial DAX measures was not working, so I rewrote it with optimized logic.

• Even after optimization, the card visual keeps loading continuously and shows an error — what should I do?

Please find the following dax which is optmized dax code and the format of code is written in the other measures as well:

Original DAX :

Patients by Chronic Condition =
CALCULATE(
DISTINCTCOUNT('Billing Master'[billinfo_patientnumber]),
FILTER(
'Billing Master',
NOT ISBLANK('Billing Master'[testmaster_specialised])
)
)

Optmized DAX for Direct Query Mode :

Patients by Chronic Condition =
CALCULATE(
DISTINCTCOUNT('Billing Master'[billinfo_patientnumber]),
KEEPFILTERS(NOT ISBLANK('Billing Master'[testmaster_specialised]))
)

Error/Issue Screenshot -

Could anyone please help in resolving the issue? 

@Anonymous @fabriccommunity 

[lbendlin]

The initial DAX measures was not working, so I rewrote it with optimized logic.

How do you know it is optimized? Did you check the query plan and server timings with DAX Studio?

[v-menakakota · Accepted solution]

Hi @pranjalagarwal1 ,
Thanks for reaching out to the Microsoft fabric community forum. 

Thanks for sharing the details,  since you are working with ~7–8 crore rows in DirectQuery mode, the behavior you're seeing is often related not only to the DAX logic but also to the underlying source performance, query folding, and overall model design. Even though you optimized the DAX expression, DirectQuery pushes most work down to the SQL source, so it's important to validate that the query being generated is efficient and the source has appropriate indexing. I would recommend reviewing Microsoft's official DirectQuery guidance.

DirectQuery model guidance in Power BI Desktop - Power BI | Microsoft Learn

Optimization guide for Power BI - Power BI | Microsoft Learn

Best Regards, 
Community Support Team 

[v-menakakota]

Hi @pranjalagarwal1 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

Best Regards, 
Community Support Team  

[v-menakakota]

Hi @pranjalagarwal1 ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

Best Regards, 
Community Support Team