Forum Discussion

jhauw74's avatar
jhauw74
Helper I
1 year ago

Displaying raw data using inactive relationship

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.



 

Need Help

3 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    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's avatar
      jhauw74
      Helper I
      1 year ago

      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's avatar
        danextian
        Super User
        1 year ago

        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.