Directquery measure help

[askme1217]

I have few measures like this below for my import mode datamodel. When i switch to directquery, these measures wont work.
How do i write this in directqiery.

Total=

CALCULATE(

    DISTINCTCOUNT('STU_ENR'[ST_ID]),

    FILTER('SECT',[CFLAG]= "Y" )

)

[v-saisrao-msft]

Hi @askme1217,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you.

[v-saisrao-msft]

Hi @askme1217,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @askme1217,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.
Thank you.

[v-saisrao-msft · Accepted solution]

Hi @askme1217, 

Thank you for reaching out to the Microsoft Fabric Forum Community. 

Thank you @lbendlin, for your insights. 

The issue you're facing is due to how DirectQuery handles DAX expressions differently from Import mode. In your original measure, the use of FILTER ('SECT', [CFLAG] = "Y") can prevent query folding, which is essential in DirectQuery for performance and compatibility. Try replacing the FILTER function with a direct Boolean filter, like this: CALCULATE(DISTINCTCOUNT('STU_ENR'[ST_ID]), 'SECT'[CFLAG] = "Y"). 

This approach allows Power BI to generate efficient SQL queries that are more likely to fold. Also, ensure that a proper relationship exists between the STU_ENR and SECT tables and enable "Assume Referential Integrity" if possible, as this helps Power BI push filters using inner joins. Lastly, while the blog post shared relates to memory usage in Import mode, it’s still relevant—DISTINCTCOUNT is resource-intensive, and in DirectQuery, ensuring proper indexing and query optimization on the data source side is just as important.

If this post helps, then please give us ‘Kudos’ and consider accepting it as a solution to help other members find it more quickly.

Thank you. 

[lbendlin]

This might give you some ideas (also check the comments)

Memory overhead of distinct count measures in Power BI