Is there a way make the inactive relationship active?

[ljlmsd]

 

Hi community,

I need your help! I am pretty new to the PBI, which is a very powerful tool.

I want to use the date table in the red circle as a slicer to control two main fact tables. But the relationship is inactive.

The two fact tables are basically the same with the same fields. Is there a way to achieve this? Or do I need to change the structure of the model?

Please shoot me a video if you have any recourses to teach how to structure the relations.

Thank you!

[mahoneypat]

I agree you should rebuild your model, but in the short term you can use USERELATIONSHIP() inside a CALCULATE() expression to turn on a relationship.  See this article

https://docs.microsoft.com/en-us/dax/userelationship-function-dax

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[edhans]

@mahoneypat correct me if I am wrong, but that won't affect slicer control will it? I guess it depends on what the measure and slicer are doing. I know I hit this issue a year or so ago and nothing but activating the relationship worked for the slicer, but I do not recall the specifics of what I was slicing - a DIM value or a calculation in a measure. 

[mahoneypat]

You are right @edhans .  I was just answering the part about activating the relationship.  The model should be rebuilt, but TREATAS() could also be considered temporarily to pass filters around the model to get the desired effect.

Regards,

Pat

[aj1973]

Hi,

Is there a reason for having 2 Calendar Tables! wht don't use only one of them and you will be set.

Regards

[edhans]

You should really redo the model. There is rarely a need for 1:1 relationships, and quite a few bi-directional relationships, which even Microsoft recommends against as a rule. You are better off just merging the data in Power Query and having 1 table. You have a ton of 1:1 relationships where it looks like you brought in a lot of tables and loaded them. 

You want to try to create a star schema. The reason your relationship is inactive is you have ambiguity in your model because it is a big circle of tables related. The model multiple ways to get from table to table, and that is generally not a good thing. It disables relationships when that happens. 

[ljlmsd]

Hi Edhans,

Thank you for pointing it out! I feel the same way as you do. At first, I was trying to append the two similar tables and unpivot it.

But when I created a new measure to compare the value of this year and last year, I draw the measure and the date to the measure, nothing shows up. 

Do you know why this happen?

[edhans · Accepted solution]

Make sure you have marked the Calendar table as your date table by right-clicking on it in the report view, then mark as date table. Then ensure that your date intelligence function is using that date as the [Dates] column for its work. It won't work if you use other dates in the model.

[ljlmsd]

Thank you! 

Problem solved!

I really appreciate it!

[edhans]

Great @ljlmsd - I had to learn the hard way that the date intelligence stuff only works reliabily, or at all, with the magical date column in the date table.