Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
Many thanks.
Solved! Go to Solution.
@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.
@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.
@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.
@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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
86 | |
67 | |
49 |
User | Count |
---|---|
134 | |
113 | |
100 | |
68 | |
67 |