Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Filtering with an active relationship while grouping with an inactive relationship

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 ill...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Anonymous ,

 

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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!