Can't create a direct active relationship ... ambiguity between tables (Date table)

[Jop1235123]

Hi all, 

For one of my dashboards i created the following model:

Planned terms and invoices are connected with orders with the column 1. ordernumber.

Beside that the terms and invoices tables are connected with the date table.

For one of my 'table overview' i want to connect orders with the date table. I have a slicer which shows the bookyears and i want to filter the 'order overview' in my dashboard based on the year ready. The result i want to show is a table which only shows the orders ready in the selected year with the slicer. This is of course not possible because of the already established relations between the tables (ambiguity between tables). I can't connect the order overview with the terms or invoice table because this are no dimension tables. 

Someone got a good solution for this? 

Thx in advance.

Jop 

[Sahir_Maharaj · Accepted solution]

Hello @Jop1235123,

Can you please try the following:

1. Use Userelationship in Your Measures

SumOfOrdersByYearReady = 
CALCULATE(
    SUM(Orders[OrderValue]),
    USERELATIONSHIP(Orders[YearReady], 'Date'[Date])
)

2. Creating a Disconnected Slicer Table

YearTable = DISTINCT ( SELECTCOLUMNS ( 'Date', "Year", YEAR('Date'[Date]) ) )

Then, you can use a measure to filter the orders based on the selected year from this new slicer

SelectedYear = SELECTEDVALUE ( YearTable[Year] )

OrderValueByYear = 
CALCULATE (
    SUM ( Orders[OrderValue] ),
    FILTER (
        ALL ( 'Date' ),
        YEAR ( 'Date'[Date] ) = [SelectedYear]
    )
)

Use this new measure in your visual instead of the direct column from the 'Date' table.

Hope this helps.

[Jop1235123]

Thank you once again for the great assistance Sahir! Fixed the issue. 

[v-jialongy-msft]

Hi @Jop1235123 

Hope the following methods can solve your problem:

You can create a separate date table specifically for the 'orders' table. This table would only include dates relevant to orders and could be linked directly to the 'orders' table. This allows you to filter orders by book year without interfering with the existing relationships between other tables.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jop1235123]

Hi Jayleny,

Thanks for your reaction. 

This isn't the solution i am looking at. I am filtering with a year slicer and it has to interact with all the overviews on my dashboard. Otherwise i have to make 2 year slicers next to each other. I want one year slicer connected to all my overviews on the page. 

[lbendlin]

I would argue that orders and invoices are independent facts and should not be connected directly. Instead, orders should be connected to the calendar, and the invoices for an order can be accessed via TREATAS.