USERELATIONSHIP not working - why?

[D_PBI]

Hi.
My below DAX measure is not returning the correct numbers and I believe it is due to the USERELATIONSHIP not working as expected.

There are two tables in question here: dimDate and agreement. There are multiple relationships between the two of them. The two relevant ones are:
dimDate[Date] <> agreement[Execution Date] - the Active relationship.
dimDate[Date] <> agreement[Received Date] - the Inactive relationship.

The measure is focusing on agreement[Received Date] which is the inactive relationship. However, when I make the dimDate[Date] <> agreement[Received Date] the active relationship, then numbers returned are correct. This is why I believe the USERELATIONSHIP function isn't working.

Please can someone explain why this is and help me get my measure working as needed?
Thanks.

[v-pgoloju]

Hi @D_PBI,

Just following up to see if the solution provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.

Best regards,

Prasanna Kumar

[D_PBI]

@mark_endicott thanks for your reply.
Your suggestion of creating a data table per date relationship is something I have thought to do but I was hoping to understand why my USERELATIONSHIP isn't working. I would need to create several indivdual data tables that all feed into a single date table and it's this single date table that is used in the slicer visual on the page. The page has several line graphs which all use different relationships but I'm trying to allow the user only needing to filter one date slicer visual - if this makes sense.
Before I go down this path, I'll show you what's happening. I've remove other line graphs so to focus just on the Execution Date and Received Date relationships.

This is the model showing the relationships between the dimDate and agreement tables. The active relationship is dimDate[Date] <> agreement[Execution Date].

The measure used in the AUTM Agreements Graph is below. Notice, even though the dimDate[Date] <> agreement[Execution Date] relationship is the active one, I've used USERELATIONSHIP too (I note you state it won't be used as the relationship is active). This measure/graph is correct in its output.

The measure used for the Consultancy Agreements graph is below. This measure uses the inactive dimDate[Date] <> agreements[Received Date] relationship. This measure/graph is incorrect in its output.

Now altering the relationships to make the dimDate[Date] <> dimDate[Execution Date] inactive (so all relationships between dimDate and agreement are inactive), but leaving both measures untouched, changes the graphs to show the below.

See how the graphs have become non-cumulative totals. This shouldn't be the case - they should be cumulative.
Some questions here:
1) Why is setting the dimDate[Date] <> agreement[Execution Date] to inactive affecting the graph when the measure uses USERELATIONSHIP?
2) The original question, why isn't the dimDate[Date] <> agreement[Received Date] measure not working as expected?

Surely, this is why the USERELATIONSHIP is available, to handle requirements such as this.
Any help will be great. Thanks.

[mark_endicott]

@D_PBI - your measure for the Consultancy Agreements graph is incorrect when you have the dimDate[Date] <> agreement[Execution Date] active, because this relationship is still filtering the data even though you have used USERELATIONSHIP to activate the other inactive relationship. This is why I suggest de-activating all date table relationships. 

Once you have done this, you need to reconfigure how your filters work, in order for the correct context to be used in your cumulative totals. I would suggest chaining your measures together. This means splitting your logic out into two measures. See below:

Measure 1 = CALCULATE( DISTINCTCOUNT ( item ), USERELATIONSHIP( dimDate[Date], agreement[Execution Date] ))

Measure 2 = CALCULATE ( [Measure 1], FILTER ( ALLSELECTED ( Date logic........

This ensures the filter context for date is already applied when you ask the 2nd measure to route through your dates. 

I would also suggest you mark your dimDate as a date table, this helps Power BI understand where time intelligence is being used. 

If you still can't get this to work and need me to help any more, you will need to supply some anonymised test data in the exact structure of your model so I can do some testing of my work, and send you back an example file that shows you how this works.

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[D_PBI]

@mark_endicott thanks for your reply and that's a good question.
I made the dimDate[Execution Date] <> agreement[Received Date] inactive. So all relationships between dimDate and agreement were inactive relationships. The results was, on my page I have other line-chart visuals, the line-chart visuals that use a (any) relationship between dimDate and agreement all altered and all became non-cumulative line. You'll note the measure is to calculate a cumulative total. So, yes, I think the active relationship was having some affect.

Having re-made the dimDate[Date] <> agreement[Execution Date] active again, I attempted your REMOVEFILTERS suggestion but didn't make the slighest difference. The line graph's number remained the same.

Just to add. If a date dimension table and another table have more than one relationship present, I tend to include USERELATIONSHIP even for the active relationship.

Do you have any furthers ideas?  Thanks.

[mark_endicott]

@D_PBI - yes, if you make all relationships between dimDate and agreement inactive, you will need to add a USERELATIONSHIP clause in every measure that will specify which one it should use. This will work with cumulative measures too. I can only assume you did not do this, so this is why allow your other charts broke.

Also USERELATIONSHIP only works on inactive relationships, so there is no need to use it in a measure that follows an active relationship. 

As a suggestion, if you can't work out why USERELATIONSHIP is not working for you, perhaps it would be better to use two date tables. One for each date and both connected through Active relationships. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[mark_endicott]

@D_PBI - it is possible that the active relationship with ExecutionDate is still pasing a filter onto your table. 

You can try using two inactive relationships (this would always be my suggestion when using USERELATIONSHIP() - but you will need to ensure all your measures then specifiy which relationship they will use) OR you could try adding REMOVEFILTERS( agreement[Execution Date] ) as an additional filter condition on your DAX measure. This should remove any filter context that is being placed through your active relationship.

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!