Forum Discussion
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' 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.
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
3 Replies
Hi Julia, when you say "not working" would you please give more details on exactly what is going wrong?
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!
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
