Forum Discussion

TaroGulati's avatar
TaroGulati
Helper III
10 months ago
Solved

Use relationship total issue

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

 

Need Help
  • v-sgandrathi's avatar
    v-sgandrathi
    9 months ago

    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.

7 Replies

  • Praful_Potphode's avatar
    Praful_Potphode
    Super User
    10 months ago

    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

     

      • v-sgandrathi's avatar
        v-sgandrathi
        Community Support
        9 months ago

        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's avatar
      v-sgandrathi
      Community Support
      9 months ago

      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!