Showing results for 
Search instead for 
Did you mean: 
Advocate V
Advocate V

Filter generate is very slow in DirectQuery Mode

I have two Tables, Budget (dimension Table) and Installed Quantity (Fact Tables), linked as multiple to Multiple.

Installed Quantity is DirectQuery as it change frequently

Budget is import as it change only once a month.


to calculate Value Achieved, I am using this measure


Value Achieved=
VAR _Table =
    FILTER (
        GENERATE ( Budget_Resources, Installed_Qty ),
        Budget_Resources[PK] = Installed_Qty[PK]
    SUMX ( _Table, [Resource Net amount/unit] * [Installed_Qty] )


when in import mode for both, it works fine, but when I changed Installed QTY to DirectQuery, it is become extremely slow, 2 minutes to render ? is there a better way 



Advocate V
Advocate V
Advocate V
Advocate V
Super User
Super User

@mim , are these tables not joined in the model on PK, what is the need for generating it in the measure?

If they are already joined on PK then avoid this GENERATE, if you need multiple column join use combinevalues

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

@amitchandak  how to get a table, that containes two columns from two tables, they are linked on multiple to multiple, so I can't use related ?

Helpful resources

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.