Help with adding column to table (one-to-many relationship filter)

[_Patrick]

Hello,

Can someone please confirm if the following is achievable using Power BI? I have two tables that have a One-to-many relationship. I am trying to add a new column to Table A (one) which will contain the most recent Event Name from Table B (many).

Desired output - new column on Table A which contains the latest most recent Value from the Many side.

Thanks in advance! 

[serpiva64 · Accepted solution]

Hi,

to obtain this table

you have to:

In Table B

- Group by 

- then add custom column

- expand Custom

and now Table B looks like this:

You can remove Column Count.

Then in Table A:

- Merge query

- and finally Expand Table B

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[_Patrick · Accepted solution]

Thanks for the suggestions! This is really helpful! Another way I discovered was to add a new column using DAX:

LatestEvent = var _id = [ID]
var _lastDate = CALCULATE(MAX('Table B'[Date]),'Table B'[ID] = _id)
return
calculate( MAX('Table B'[Name]),'Table B'[Date] = _lastDate && _id = 'Table B'[ID])

[serpiva64 · Accepted solution]

Hi,

to obtain this table

you have to:

In Table B

- Group by 

- then add custom column

- expand Custom

and now Table B looks like this:

You can remove Column Count.

Then in Table A:

- Merge query

- and finally Expand Table B

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

[KNP]

Hi @_Patrick,

Depending on how the tables are built, one way to do this is to reference Table B/aggregate to max date/merge back with Table A.

See attached PBIX.

Hope this helps.