Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RobertO995
Frequent Visitor

Data model optimization

Hello all,

 

I'm facing new challenges concern reporting in my work. The easiest reports are already made and working quite well. New reports I need to prepare will be very detailed what means more data loaded into model. The only way I can load data to my model is using Analysis Services (I cannot ude SQL to upload data). Due to amount of data, I try to limit it by using MDX (to have only the data I need, nothing more).

Sometimes I cannot skip data size topic, because my data model needs to be big, but I can consider optimization on the querry or DAX side (I mean, to avoid the situation when the user is selecting the slicer and visualization is refreshing so long).

Can you share with me the best practises to have effective report?

I keep in my models the star schema, but I think, that there a much more knowledge about optimization models in PBI.

I'm looking for detailed information like for e.g. "beeter use RELATED() function, than LOOKUP() when you have big tables" - don't know whether it's true, but I think it's like that. 😉

What type of operations is better to make in querry and what in DAX? For example filtering the table? Is it better to filter it in querry or use FILTERTABLE() function in DAX?

To be precise, what type of operations/functions "costs" the most of effectiveness? Is groupby function "cheap: for model etc.?

Do you have any value tips for me? Or maybe you can share with me any articles/books? I'm focused on model optimization.

 

I will be thankful for any interesting information. 🙂

 

Regards,

Robert

 

1 REPLY 1
amitchandak
Super User
Super User

for Related refer : https://databear.com/dax-related-table-functions/

In case there is some data that is not required in reporting either delete to filter it in edit query/data transformation mode.

Guidelines: https://docs.microsoft.com/en-us/power-bi/guidance/

Refer this : https://community.powerbi.com/t5/MBAS-Gallery/bd-p/MBAS_Gallery

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.