Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

USERELATIONSHIP with MIN date not working as expected

 

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

 

 

 
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

    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