Forum Discussion

Jop1235123's avatar
Jop1235123
Frequent Visitor
2 years ago
Solved

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

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 

 

 

Need Help
Show and Tell
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Jop1235123
      Frequent Visitor
      2 years ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        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.

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Jop1235123
      Frequent Visitor
      2 years ago

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