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 ta...
  • Sahir_Maharaj's avatar
    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.