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

[TaroGulati]

@v-sgandrathi can you send please the PBIX created by you?

In the attachment i see the PBIX sent by me. 

Thanks

[v-sgandrathi]

Hi @TaroGulati,

Apologies for the confusion earlier, it looks like I had mistakenly attached your original PBIX file in my previous message instead of the one I created. I’ve now attached the correct PBIX file that I prepared. Please check the updated attachment and let me know if everything looks good on your end.

Thank you for your patience and understanding!

[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 

[v-sgandrathi · Accepted solution]

Hi @TaroGulati,

After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used the sample data on my end and successfully implemented it.  I am also including .pbix file for your better understanding, please have a look into it:

Hope this will help you resolve your issue, if you need any further assistance, feel free to reach out.
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

For your first question, you can handle multiple inactive relationships more efficiently by creating a calculation group rather than relying on a lengthy SWITCH statement. Since Power BI Desktop doesn’t currently allow you to create calculation groups directly, you’ll need to use Tabular Editor for this task. This method lets you activate the necessary relationship dynamically for each measure, making your DAX code cleaner and easier to manage.

Thank you for using Microsoft Community Forum.

[v-sgandrathi]

Hi @TaroGulati,

Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.

[v-sgandrathi]

Hi @TaroGulati,

Just checking in -- have you had a chance to review and try the provided solution? Kindly share the status whenever you get a chance.

Looking forward to your response.