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

[RenateBK]

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?

[Thejeswar · Accepted solution]

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 · Accepted solution]

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,

[v-veshwara-msft]

Hi @RenateBK ,

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

[v-veshwara-msft · Accepted solution]

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.

[Ashish_Mathur]

Hi,

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

[RenateBK]

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.

[Ashish_Mathur]

Sorry but i really do not understand your response.

[RenateBK]

I totally understand that, I'm a bit lost here so I might be explaining the situation incorrectly.

When I try to make all relationships active, this is the message I receive and I'm not sure how to fix it besides using inactive relationships. 

[v-veshwara-msft]

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.

[Thejeswar · Accepted solution]

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]

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]

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

[RenateBK]

Thank you for the feedback! I think I've run into one slight issue (or I am messing this up somehow). This is how I wrote the DAX code:

Caserelationship = SWITCH(TRUE(),

SELECTEDVALUE(FactBeds[IDKey]) = "Beds", DISTINCTCOUNT(FactsBeds[IDKey]),

SELECTEDVALUE(FactTables[IDKey]) = "Tables", CALCULATE(DISTINCTCOUNT(FactTables[IDKey]), USERELATIONSHIP(DimCountry[IDKey], FactTable[IDKey])))

I get an error saying that the "FactTable" Tables cannot be used for the as it's comparing Text with Integer, however I have doublechecked the relationship table and the IDkeys should all be set to "Whole number", and the keys used for establishing the relationship are all the same type of format.

Am I messing up with my column reference somehow?

[Thejeswar · Accepted solution]

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,