Use relationship total issue

[TaroGulati]

Hi all, 

I am trying to find better way to manage different columns in the userelationship function. In my case i have to use columns category sales and categor warehouse when they are in scope. I created the inactive relationships as you can see below:

In the DAX i am managing this way and results you can see in the below screen:

I have two difficulties here:

1. Is there other way to manage userelationship instead of using a swtich statement? In the real case we have 9 different inactive relationship and we have to repeat whole calculation 9 times so code is very long.
2. Using the switch statement, totals are not working correctly. Any suggestion on that how to manage without sumx or summarize which take alot of calculaton with large tables?

I attached also the PBIX used.https://1drv.ms/u/c/93e25d0f6aea0183/Efvx13FLH0NDrWqi49aYQk4BEWhfdp-a_MDA2S3bhhmd5g?e=NRxZUU 

Thanks

[Praful_Potphode]

Hi @TaroGulati ,

Can you try creating composite keys which can be combination of columns used in relationship and then try to join it.

You can try below dax to create columns in fact and dimensions and then join them using relationships.

ck = Dim[Category Sales] & Dim[Category Finance]&Dim[Category Warehouse]
//create this in DIm table

ck = 'Fact'[Category Sales] & 'Fact'[Category Finance]&'Fact'[Category Warehouse] //create this in fact table

join them using relationship as shown below.

Fact(CK)    1<----->1 Dim(CK)

relationship cardinality may vary based on your data.

Please give Kudos or mark it as resolved once confirmed.

Thanks and Regards,

Praful Potphode

[TaroGulati]

Hi @Praful_Potphode thanks for the feedback but in my case composite key can't work