Help with Modelling with Inactive Relationships

[Katie-Farrand12]

Good day all,

I have a model with 2 tables (for simplicity):

1. Warehouse table  - where "Warehouse" is what we use to describe where we can transfer & hold stock

2. Transaction Table, structured like this:

I have an active relationship between "To Warehouse" field and the Warehouse Table, and an inactive relationship between "From Warehouse" and the Warehouse Table. 

I have built measures using DAX USERELATIONSHIP for transactions related to the "From Warehouse" column. When I add a slicer from the warehouse table to the visualisation, and I filter on "Outlet 1", the data table is filtering perfectly as above. I can see all the transactions for Outlet 1, regardless of whether it is "To Warehouse" or "From Warehouse". 

However, when I add the slicers for the other fields, they are not working as expected. I can only see options related to the active relationship (i.e. "To Warehouse").  I have pulled the slicers from the transaction table.

Based on the above example:

Slicer 1: "Transaction Type" - I can only see "Dispatch" and "Transfer", I cannot see "Return"

Slicer 2: "Reference Number" - I can only see "123" and "456". I cannot see the other 2 options in the slicer. 

I tried to make "To Warehouse" > Warehouse Table and "From Warehouse" > Warehouse Table relationships inactive, adding in the USERELATIONSHIP for all measures. However this breaks the Warehouse Slicer (worse than when I started). This did not work. 

Does anyone have any suggestions of how to resolve this?

Thanks,

Katie

[Katie-Farrand12]

Hi @Bibiano_Geraldo 

Thank you for your response!

So because I have an active relationship between the TO WAREHOUSE and the Warehouse Table, I'm guessing I do not need to add the USERELATIONSHIP and ALL functions?

I tried to add it but my slicers are still not working as expected. I have added my DAX below for further context, can you spot anything I am doing wrong?

Here is my calculation for QTY ISSUED:

Total QTY Issued =

    CALCULATE(

        SUM('VIEW_IBT'[QTY Issued KG]),

        'VIEW_IBT'[Outlet Transaction Type] = "Dispatch" )

+

CALCULATE(

    SUM('VIEW_IBT'[QTY Issued KG]),

    USERELATIONSHIP('VIEW_IBT'[From Whse Code], 'DIM WhseMst_Outlet'[Warehouse_code]),

      ALL('VIEW_IBT'[Outlet Transaction Type]),

    'VIEW_IBT'[Outlet Transaction Type] = "Return"

)

+

CALCULATE(

    SUM('VIEW_IBT'[QTY Issued KG]),

    USERELATIONSHIP('VIEW_IBT'[From Whse Code], 'DIM WhseMst_Outlet'[Warehouse_code]),

         ALL('VIEW_IBT'[Outlet Transaction Type])

    'VIEW_IBT'[Outlet Transaction Type] = "Outlet Transfer"

) +

CALCULATE(

    SUM('VIEW_IBT'[QTY Issued KG]),

    'VIEW_IBT'[Outlet Transaction Type] = "Outlet Transfer"

)

________________

For QTY RECEIVED, my calculation is as follows:

Total QTY Received =

    CALCULATE(

        SUM('VIEW_IBT'[QTY Received KG]),

        'VIEW_IBT'[Outlet Transaction Type] = "Dispatch"

 +

CALCULATE(

    SUM('VIEW_IBT'[QTY Received KG]),

    USERELATIONSHIP('VIEW_IBT'[From Whse Code], 'DIM WhseMst_Outlet'[Warehouse_code]),

      ALL('VIEW_IBT'[Outlet Transaction Type]),

    'VIEW_IBT'[Outlet Transaction Type] = "Outlet Transfer"

) +

CALCULATE(

    SUM('VIEW_IBT'[QTY Received KG]),

    'VIEW_IBT'[Outlet Transaction Type] = "Outlet Transfer")

+

CALCULATE(

    SUM('VIEW_IBT'[QTY Received KG]),

    USERELATIONSHIP('VIEW_IBT'[From Whse Code], 'DIM WhseMst_Outlet'[Warehouse_code]),

      ALL('VIEW_IBT'[Outlet Transaction Type])

    'VIEW_IBT'[Outlet Transaction Type] = "Return"

)

Here is an example of the visual in Power BI, filtered for Northmead Outlet.

The "Outlet Transaction Type" slicer is only showing "Dispatch" and not "Return" and "outlet Transfer".

[Bibiano_Geraldo]

Hi @Katie-Farrand12 ,
When you use USERELATIONSHIP in DAX measures, it activates the inactive relationship for those specific measures, but the slicers are still being filtered based on the active relationship. This is why you're seeing only values related to the active relationship in the slicers.
Create measures that explicitly account for both relationships using USERELATIONSHIP for your inactive relationship. For example:

Total Quantity = 
CALCULATE(
    SUM('Transaction'[QTY (KG)]),
    USERELATIONSHIP('Transaction'[From Warehouse], 'Warehouse'[Warehouse]),
    ALL('Transaction'[Transaction Type]) -- Remove filters from 'Transaction Type' slicer
)

Or you can create a custom measure that aggregates both From Warehouse and To Warehouse:

Total Transactions = 
CALCULATE(
    SUM('Transaction'[QTY (KG)]),
    USERELATIONSHIP('Transaction'[From Warehouse], 'Warehouse'[Warehouse]),
    USERELATIONSHIP('Transaction'[To Warehouse], 'Warehouse'[Warehouse])
)