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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
jakesh
Frequent Visitor

Please Help with Cross Filtering Options in Many-to-Many Relationship

Hello!

I am trying to leverage my d_Customer and d_OrderType to create a relationship between d_OrderStatus and f_Sales so that I can view a table containing customer, ordertype, status, and cost and have the ability to slice it by status.

 

I am trying to avoid a many-to-many relationship for crossfiltering, and I am avoiding using a CROSSFILTER() measure becuase I would like to use Status as a slicer. I am also trying to avoid to create a Status column directly in Sales as a Calculated Column or a SQL join becuase it would sacrifice performance badly since Status only rolls up to the patient and ordertype level.

 

Any recommendations on how I can do this "correctly" or better?

 

PBIX File

jakesh_7-1692915517011.png

 

jakesh_6-1692915395555.png

 

Many thanks.

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@jakesh first this is the right approach. Indeed merging will add to the data load but make everything simple afterwards. There are always 2 schools of thought, prepare data in a shape that DAX is easy and everything works seamlessly, or create a poor design, and then struggle with DAX and have performance challenges. The choice is yours.

 

If your source is some database, I would recommend having a SQL "view" in the database pull the joined data from the view, and then you don't have to do the merging in PQ which will speed up the process, if your source is not a database where you can do the view/join then this is the right approach.

 

End of the day if you have a schedule refresh in the Power BI service, even if takes a little bit extra time, it is not the end of the world but think how easy everything will be afterward.


Good luck!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

@jakesh you did the right thing. Cheers!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

@jakesh first this is the right approach. Indeed merging will add to the data load but make everything simple afterwards. There are always 2 schools of thought, prepare data in a shape that DAX is easy and everything works seamlessly, or create a poor design, and then struggle with DAX and have performance challenges. The choice is yours.

 

If your source is some database, I would recommend having a SQL "view" in the database pull the joined data from the view, and then you don't have to do the merging in PQ which will speed up the process, if your source is not a database where you can do the view/join then this is the right approach.

 

End of the day if you have a schedule refresh in the Power BI service, even if takes a little bit extra time, it is not the end of the world but think how easy everything will be afterward.


Good luck!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you @parry2k . Doing the SQL view and joining the status field into the fact table worked well. 

parry2k
Super User
Super User

@jakesh I think you need to get rid of d_OrderStatus and bring the Status from this table into f_sales table. This can be done in the PQ by merging two tables on customer and Order Type column or if there is another key that can be used to combine these. Once this is done, you will have a relationship on d_status with f_sales and your model will be perfect. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Many thanks @parry2k . The problem I encountered with your proposed solution is that, at scale, it doubled the amount of time it takes to load the data because the fact table will need to join d_status over every single customer many many times since the customer can have many orders across many dates. The real case I am working on has the same problem but with 600K+ rows and 20 columns. 

 

I wonder if I should create a dedicated fact table to just working with customer-level (f_Customer) data to be able to reuse my dimension tables, and if that would yield better benefits in the future for customer-level analyses. This is considering the fact that measures will help with agreggating f_Sales data on f_Customer.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.