Filtering with an active relationship while grouping with an inactive relationship

[JeanMartinL]

Hello everyone,

I'm trying to slice and dice data using both an active and inactive relationship at the same time and I feel like I'm missing something.

I've created a very simple example to illustrate my issue. I feel like this should be a pretty commun scenario.

2 Tables: Location and Movement. Location is a simple ID/Name list while Movement has FromLocationID, ToLocationID and Quantity. There are 2 relationships between the tables, the active relationship is on FromLocationID and ToLocationID is inactive.

What I'm trying to do sounds pretty simple. I simply want to filter on the FromLocation (using a slicer for example) and then have the results grouped by ToLocation.

Here is what I have done so far:

I wonder if I can somehow create a measure that filters first using the active relationship and then applies the grouping using USERELATIONSHIP as a second step but I have no idea how that would work.

The only "solutions" I can think of now are pretty bad IMO .

1) Doubling the Location table so that I can have a active relationship per column.

2) Creating calculated columns with RELATED to have the LocationName directly in Movement

Keep in mind that this is a contrived example so that it's simpler to understand. In the real model the "Location" table is a large dimension with many fields and other relationships, so doubling the table or copying fields in the fact table is not really an option.

Thanks for the help!

[Anonymous]

Hi @JeanMartinL ,

Please create a new table for slicer:

Table = 'Location'

Model:

Then create a new measure:

Tottal Quantity =
VAR __location_id =
    CALCULATETABLE (
        VALUES ( 'Table'[LocationID] ),
        ALLSELECTED ( 'Table'[LocationName] )
    )
VAR __to_location_id =
    CALCULATETABLE (
        VALUES ( 'Movement'[ToLocationID] ),
        FILTER ( ALL ( 'Movement' ), 'Movement'[FromLocationID] IN __location_id )
    )
RETURN
    IF (
        MAX ( 'Location'[LocationID] ) IN __to_location_id,
        CALCULATE (
            SUM ( Movement[Quantity] ),
            'Movement'[FromLocationID] IN __location_id,
            USERELATIONSHIP ( 'Location'[LocationID], 'Movement'[ToLocationID] )
        )
    )

Output:

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[JeanMartinL]

Hello @Anonymous 

Thank you for the response. This indeed works in this contrived example, but I feel it would be inelegant in a real model / report.

Since we need to use that copy of Location for the slicer and that it only affects a single measure, it means that all other visuals will not be filtered by it (especially if we are filtering other fact tables). I fell it also add a lot of confusion into the model.

If I were to add a copy of a table, I would rather create a “LocationDestination” table with a relationship to “ToLocationID”, I believe this would be slightly clearer and could still work with the basic Slicer on “Location”.

Still, I want to thank you for that measure, it is some impressive DAX work, and I could see it being useful.

Thanks!