Displaying raw data using inactive relationship

[jhauw74]

Hi Experts,

Would like to get some help if there's a way I can use a calendar based slicers that using inactive relationships?

Here's the scenario:

Active connection is using created date  column on b2b table

Inactive connection is using closed date column on b2b table

I want to be able to  display the raw data on b2b table using the inactive connection when the slicer for status = closed:

However as the active relationship is using created date when the filter status = closed it generates incorrect row counts.

Is there a way to solve this issue? Thanking all in advance.

[danextian]

Hi @jhauw74 

You can invoke the inactive relationship by using CALCULATE and USERELATIONSHIP in a measure. Example.

CALCULATE ( [my total], USERELATIONSHIP ( Dates[Date], FactTable[Closed Date] ) )

Attached is a sample pbix that uses such function.

[jhauw74]

Thanks @danextian - yes I have been using that DAX function which working perfectly on measures, however when it comes to visualisation ie. using table to display raw data this is where I think that DAX function not working because the date filter need to use active relationships if that make sense.

[danextian]

Inactive relationships will not change the displayed rows.  You can either use a measure that uses USERELATIONSHIP as a visual filter, keep it in the visual and just make the text invisible or use another dimension table that has an active relationship to the other column.