Inactive Date Relationship

Power BI Data Model
1

Hi everyone I am hoping someone might be able to help me with this one. I have a very simple model and I am trying to have the date table have two relationships with two different fact tables. However on the second relationship it continues to be a broken line and an inactive relationship for some reason.

Does anyone know why this is happening and how I might be able to fix it?

Inactive Date Relationship
Inactive Date Relationship768Ă—612 26.3 KB

Thanks
Ronan

2

@Ronan

Because you have a Many : Many relationship between Credit Risk Scores and Airline Master Sheet with a Bi-Directional Cross Filter

If you apply a filter on the Dates table:

  1. It will reach Credit Risk Scores and then doesn’t stop there, after that it will reach Airline Master Sheet and then it will Reach Accipiter table

  2. If it was possible to allow an active relationship between Dates and Accipter then Dates will filter Accipter but now the engine will get confused, should a filter applied to Dates table follow the longer path listed above or just directly go to Accipter table?

The old layout doesn’t show the bi-directional cross filter:

image
image1823Ă—792 68.4 KB

New one does:

image
image980Ă—587 22.4 KB

Change the cross filter direction to Single and you should be good

1 Like
3

Thank you @AntrikshSharma,

So going to a Many to One filter between Airline Master Sheet and Credit Risk scores does allow the date filter to Accipiter become active.

However it then throws off my filtering in the my model.

Let me try better articulate the issue/what I am trying to acheive:

  1. Master Sheet

image
image1585Ă—37 6.55 KB

image
image1287Ă—48 7.05 KB

This is a static sheet that includes one data base code per row and across the column is several descriptors. But there is no time series on this. One of the key filters I have is where the airlines is “owned”, “Managed” or “No Exposure”.

However over time the exposure I have to a single database code will have changed due to the shift in the customer base.

Therefore I need the be able to filter back into this sheet by owned, managed and no exposure from the credit risk score

  1. Credit Risk Score
    This is output from our risk models that provide a credit risk score per airline and is a tie series. It goes back to 2018 and has a score per airline in each period.

In addition it also has other filter columns with the owned, managed and no exposure being the most vital as this then allows for time serious analysis of the risk score.

  1. Accipiter NBV
    This the NBV by aircraft for each month end.
    The data includes the database code to identify the airline

What I am trying to achieve is a model where I can filter by date and by owned, managed or No Exposure and the model will then give me:
a) the credit risk score of
b) the airlines that are in the Owned and
c) the sum of NBV of that airline at
d) the filtered date

Any ideas how I might achieve this. Intuitively this seems like a fairly basic sort of approach when try to cross filter large data sets across a business.

Thanks so much
Ronan

4

In order for anyone to help with this question, a PBIX copy is going to be needed. Can you create a sample that demonstrates the problem you are having with the table filters?

It sounds like the single direction filter is possible, so perhaps something in your visual tables or DAX needs to be corrected to make this work.

5

Thanks Heather - let me quickly try do a dummy version to avoid the confidential information I have

6

Hi @Heather, thanks so much for helping.
Here is a dummy file but same issue is at play. Currently the NBV filters on airlines but cannot filter by date but if I change from many to many to single then the NBV will not filter by airlines.

Credit Risk Model Update_dummy.pbix (979.4 KB)

7

Hi @Ronan, thanks for sending in your PBIX file.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey. We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

Bumping this post for more visibility.

8

Hello everyone! Bumping this post again.

9

Thanks for the help all, I think I figures it out.

I found a video showing the userrelatiosnhip function that has managed to get it to work perfectly.
@EnterpriseDNA it would be good to tag it somewhere as I found it really hard to find but Sam had a video somewhere on active & inactive relationships and how to use userrelatiosnhip.