Unique value for joining

[Julia_Mav]

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' field, using a rule that involves selecting the 'Part Number' with the most recent 'Date Effective'.

The key value with 'Material/ValidFromYear' (25621352100/2020) is not working; see below. 

Thanks in advance.

 

[KEAlexander]

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

[Julia_Mav]

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 · Accepted solution]

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