Forum Discussion

RenateBK's avatar
RenateBK
Icon for Helper II rankHelper II
9 months ago
Solved

Possible to swap between active/inactive relationships when dealing with two unique fact tables?

Hello, 

 

My company has split up its data into separate fact tables based on case categories, however they would like to see it all in one single report.

 

I'm currently trying to create a dashboard with the overview of case  country origin related to the two most important categories for the company.  However, since there's a 1-to-1 relationship between the IDkey in both complaint cases fact tabel, and "all cases country information dim" I'm struggling with setting up the relationship/creating the measurements. 

I also am not given values, so I have to use countrows get the number of how many sent in a complaint from the different countries.

 

Currently I've tried the following:

Caserelationship = CALCULATE(DISTINCTCOUNT(beds_cases[IDKey]), USERELATIONSHIP(countrydim[IDkey], bed_cases[IDKey]))
However this doesn't filter it appropriately for the inactive relationship. 

 

My question is, is there a way to solve this or do I need to request that they make changes to the dataset?

 

 

 

Need Help
  • Thejeswar's avatar
    Thejeswar
    9 months ago

    Hi RenateBK 
    Few things I would do in this case are as follows.

     

    1. The UserRelationship() works only when there is a inactive relationship is present between the tables. Have you created it? Do check that

    2. You can handle this with a IF condition or a Switch Condition. One additional thing you would need for this is an additional slicer that defines which relationship to be used.

     

    Assuming there is a table with active relationship and another one with inactive relationship, this is how my DAX would look. I would introduce a slicer that defines which relationship to use.

    For ex, let's say we have a dummy slicer giving below options, Source1 and Source2. My requirement is to use Active Relationship when Source1 is selected and Inactive relationship when Source2 is selected

    SWITCH(TRUE(),
SELECTEDVALUE(Slicer) = "Source1", DISTINCTCOUNT(Table1[IDKey]), 
SELECTEDVALUE(Slicer) = "Source2", CALCULATE(DISTINCTCOUNT(beds_cases[IDKey]), USERELATIONSHIP(countrydim[IDkey], bed_cases[IDKey]))
)

     

    Note: You need to replace the Slicer, Source names, table names as per your requirement.

     

    Regards,

  • Thejeswar's avatar
    Thejeswar
    8 months ago

    Hi RenateBK ,

    I see in your DAX, you are equating Beds and Tables against the column IDKey, which you said is a Whole Number column. Are you sure you are doing the right thing here?

     

    If it is a Whole Number column, It may not have the values Beds and Tables.

    That is something you may have to check and fix !

     

    Hope it helps!

    Regards,

  • Anonymous's avatar
    Anonymous
    8 months ago

    Hi RenateBK ,


    Thanks for reaching out to Microsoft Fabric Community.


    As pointed out by RenateBK  the SWITCH pattern assumes a separate slicer column containing values such as Beds and Tables. That slicer column is what should be referenced in SELECTEDVALUE.

     

    In your current measure, IDKey is a numeric column, so comparing it to text values results in the text versus integer error you are seeing. This is not related to the relationship data types.

     

    To use this approach, you will need a disconnected slicer table with those category values, or otherwise consider a model change such as consolidating the fact tables or using a bridge table.

     

    Hope this helps. Please reach out for further assistance.
    Thank you.

12 Replies

  • Thejeswar's avatar
    Thejeswar
    Icon for Super User rankSuper User
    9 months ago

    Hi RenateBK 
    Few things I would do in this case are as follows.

     

    1. The UserRelationship() works only when there is a inactive relationship is present between the tables. Have you created it? Do check that

    2. You can handle this with a IF condition or a Switch Condition. One additional thing you would need for this is an additional slicer that defines which relationship to be used.

     

    Assuming there is a table with active relationship and another one with inactive relationship, this is how my DAX would look. I would introduce a slicer that defines which relationship to use.

    For ex, let's say we have a dummy slicer giving below options, Source1 and Source2. My requirement is to use Active Relationship when Source1 is selected and Inactive relationship when Source2 is selected

    SWITCH(TRUE(),
SELECTEDVALUE(Slicer) = "Source1", DISTINCTCOUNT(Table1[IDKey]), 
SELECTEDVALUE(Slicer) = "Source2", CALCULATE(DISTINCTCOUNT(beds_cases[IDKey]), USERELATIONSHIP(countrydim[IDkey], bed_cases[IDKey]))
)

     

    Note: You need to replace the Slicer, Source names, table names as per your requirement.

     

    Regards,

    • RenateBK's avatar
      RenateBK
      Icon for Helper II rankHelper II
      9 months ago

      Hello, thank you so much for the response! 

       

      I have set up the inactive relationship so this is in order. 

      One dumb question regarding the formula, what is it I am supposed to replace "Slicer" with?

      • Thejeswar's avatar
        Thejeswar
        Icon for Super User rankSuper User
        9 months ago

        Slicer should be replaced with the column you are using in the slicer that is having the values "Source1" and "Source2" in this case

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 months ago

    Hi RenateBK ,

    Thanks for reaching out to Microsoft Fabric Community.

    Just wanted to check if the response provided by Thejeswar  was helpful. If further assistance is needed, please reach out.


    Thank you.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 months ago

    Hi,

    Why in the first place do you even have an inactive relationship?  Why is that not active?

    • RenateBK's avatar
      RenateBK
      Icon for Helper II rankHelper II
      8 months ago

      Hello! 

      I tried to set it up as an active relationship as I've done that before, but the issue is here that the FactBeds table have a 1-to-1 relationship to the DimCountry table, as it's on a 1-to-1 CaseID match, similarly the FactTables table have a 1-to-1 relationship to the DimCountry table, but the FactTable and FactBeds table have no matching ID's between them.

       

      When I set up the relationship, having both of these relationships active at the same time made the numbers come out all entirely wrong, therefore I hope that USERELATIONSHIP or SWITCH potentially could prove as a solution.

       

      Worse case I will see if I can get some changes done to the datasets so that it doesn't exist as two separate fact tables, if it is not possible to solved with DAX.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 months ago

    Hi RenateBK ,


    Thanks for reaching out to Microsoft Fabric Community.


    As pointed out by RenateBK  the SWITCH pattern assumes a separate slicer column containing values such as Beds and Tables. That slicer column is what should be referenced in SELECTEDVALUE.

     

    In your current measure, IDKey is a numeric column, so comparing it to text values results in the text versus integer error you are seeing. This is not related to the relationship data types.

     

    To use this approach, you will need a disconnected slicer table with those category values, or otherwise consider a model change such as consolidating the fact tables or using a bridge table.

     

    Hope this helps. Please reach out for further assistance.
    Thank you.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 months ago

    Hi RenateBK ,

    Just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
    Thank you.