More USERELATIONSHIP Problems adding a drill through table

[DebbieE]

I am starting to think that attempting to USE USERELATIONSHIP for a none active join is a bad idea because im constantly hitting problems. Looking at the following example again

So this is the initial page using the none active Date B (And Ive already asked a question about how I deal with the slicer because that is a blocker at the moment. 

But what if I want to drill through to a page with a table?

For dateA I can simply drag date from the date table. But DateB, I cant just drag date because its connected to DateA unless I use USERELATIONSHIP.

So again Im scuppered. How do I drag dateB into this table using USERELATIONSHIP. I havent kept DateB anywhere in the model because Ive been attempting to make sure no one can access dates from anywhere but the date table

[dudeyates]

Hi @DebbieE 

You're correct that USERELATIONSHIP can get rather complex, depending on your goals. Not sure if this what you're looking for, but here's an in-depth write up with vid by Alberto Ferrari that recently helped me understand it better: https://www.sqlbi.com/articles/userelationship-in-calculated-columns/ . You might be interested in the end where he uses LOOKUPVALUE to make what was complex rather simple.

Hope that helps

James

[DebbieE]

Fabulous. thank you so much. 

Hopefully it mentions slicers and drill throughs because this was the point where I gave up and chose multiple role playing tables.

[DebbieE]

Without any more advice I have concluded that if you want to drill through to other reports and use date slicers then attempting to use a none active relationship is a none starter. I have chosen to add a new role playing date dimension instead.

[selimovd]

Hey @DebbieE ,

there is no DateB, only your values change.

You would have 2 measures:

1. FactDateA. This is using the normal relationship.

2. FactDateB. Here you change the relationship with USERELATIONSHIP.

In your report you put the date from the date table in your visual.

If you put in FactDateA you will see the value 30 on 01 Jan 2021.

If you put in FactDateB you will see the value 30 on 01 Mar 2021.

Like this, you can analyze both. But the date column you are using is from the date table and is always the same. Only the values change depending on the measure.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[DebbieE]

1. FactDateA. This is using the normal relationship.

2. FactDateB. Here you change the relationship with USERELATIONSHIP.

But these arent measures? In terms of dragging in date B how would you create that measure?

There is no identifiable measure here. Its a date. I understand how to use USERELATIONSHIP when Im creating for example Last Years Sales, Or Year to date sales. But this isnt the same