Improving report query performance

[YoungLearning]

First of all, this is an imported dataset hence Assume Referential integrity is out since the pre-requisite for that is a DirectQuery data source. Cross filter direction, set to "single" will improve the report query performance but what about Cardinality?

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solution @rajendraongole1  offered. and i want to offer some more infotmation for user to refer to.

hello @YoungLearning 

for the cardinality relationship,  the model won't provide flexibility in the ways you report visuals filter or group

And the connection mode is direct query mode, it has limitations for direct query mode, you can refer to the follwing link.

https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-many-to-many-relationships#consid...

When we have two tables that have many-to-many relationship, we often create a bridge table to create a one-to-many relationship among them, you can refer to the following picture.

2.For the cross filter, the data mode is direct query,  then using bith filer direction, you can apply the filters by propagating the filter context to a second related table on the other side of a table relationship. you can refer to the following link.

Bidirectional cross-filtering in Power BI Desktop - Power BI | Microsoft Learn

And you can refer to the following link about the advantage of star schema.

https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many#relate-many-to-many-f...

Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

Best Regards!

Yolo Zhu

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

[rajendraongole1]

Hi @YoungLearning - Basically, One-to-Many : This is the most efficient and commonly used cardinality in star schemas. It optimizes query performance because it aligns with typical analytical patterns.

try to maintain star schema design for simpler relationships improve query performance by reducing the complexity of the model.

Aggregate data at the source or during the import process to reduce the volume and granularity of the data in your Power BI model.

reference link solved:

Solved: Online Direct Query Performance - Microsoft Fabric Community

Simplify DAX calculations where possible and pre-calculate values during the data transformation phase.

Hope it helps.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[YoungLearning]

So the cardinality cannot be further reduced, that explains...

So cross filter is the most appropriate answer here?