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

KEAlexander's avatar
KEAlexander
Icon for Helper I rankHelper I
2 years ago

Hi Julia, when you say "not working" would you please give more details on exactly what is going wrong?

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