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

[jakesh]

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

Many thanks.

[parry2k]

@jakesh you did the right thing. Cheers!

[parry2k · Accepted 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!

[jakesh]

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

[parry2k]

@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. 

[jakesh]

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.