USERELATIONSHIP with MIN date not working as expected

[peternznguyen]

Hi,

I have two tables Dim_Date and Fact_Inventory, they have Active relationship based on Dim_Date(DateSK) and Fact_Inventory(ReceivedDate_DateSK) and Inctive relationship based on Dim_Date(DateSK) and Fact_Inventory(ManufactureDate_DateSK).

The Active relationship works well, of course. However, when I want to get DateBK

from Dim_Date(DateBK) by the below DAX function to get ManufactureDate:

ManufactureDate = CALCULATE(

        MIN(Dim_Date[DateBK]),

        USERELATIONSHIP(Dim_Date[DateSK], Fact_Inventory[ManufacturedDate_DateSK])

)

 and it returns DataBK based on the Active relationship "Dim_Date(DateSK) and Fact_Inventory(ReceivedDate_DateSK)" but not the expecting Inactive relationship "Dim_Date(DateSK) and Fact_Inventory(ManufactureDate_DateSK)".

DateBK column is type Date (not DateTime)

(DateSK = Date Surrogate Key,  DataBK = Date Business Key)

Screenshots 

Describe the situation

 and not expecting result

==>The ManufactureDate should return 2023-04-05 based on its ManufactureDate_DateSK 20230405, but it returns 2023-04-07 that is same as ReceivedDate

Please help me out

Thank you in advance.

Peter

[v-yiruan-msft · Accepted solution]

Hi @peternznguyen ,

Please update the formula of measure [ManufactureDate ] as below and check if you can get the expected result... Please find the details in the attachment.

ManufactureDate =
CALCULATE (
    MIN ( 'Fact_Inventory'[ManufactureDate_DateSK] ),
    FILTER (
        'Fact_Inventory',
        'Fact_Inventory'[ReceivedDate_DateSK]
            = SELECTEDVALUE ( 'Fact_Inventory'[ReceivedDate_DateSK] )
    )
)

Best Regards