Forum Discussion

Julia_Mav's avatar
Julia_Mav
Icon for Helper II rankHelper II
2 years ago
Solved

Unique value for joining

Hi everyone, I'm having trouble creating a unique key to join two tables in Power BI. I would appreciate it if someone could advise me on how to create this unique key in Power BI with the 'Material...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Julia_Mav ,

    Do you want to get the part number with the most recent effective data for per material? If yes, you can create a calculated column as below to get it, please find the details in the attachment.

    Column = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date Effective] ),
        FILTER ( 'Table', 'Table'[Material] = EARLIER ( 'Table'[Material] ) )
    )
RETURN
    IF ( 'Table'[Date Effective] = _maxdate, 'Table'[Part number] )

    Best Regards

Julia_Mav's avatar
Julia_Mav
Icon for Helper II rankHelper II
2 years ago

Hi KEAlexander, it does not provide the Part number with the most recent Effective date. It gives the value that is in B2, while B3 is needed.  Thanks!

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Julia_Mav ,

Do you want to get the part number with the most recent effective data for per material? If yes, you can create a calculated column as below to get it, please find the details in the attachment.

Column = 
VAR _maxdate =
    CALCULATE (
        MAX ( 'Table'[Date Effective] ),
        FILTER ( 'Table', 'Table'[Material] = EARLIER ( 'Table'[Material] ) )
    )
RETURN
    IF ( 'Table'[Date Effective] = _maxdate, 'Table'[Part number] )

Best Regards