Joining data from another table with specific parameters

[jp278]

Hi all,

Hope you can help me with this one as i haven't been able to find a solution despite repeated attempts. I'm a newbie so bear with me.

I have two databases/tables that is linked to in power bi

a) The first one has the primary key like below

SNo.      Unrelated data

1            Unrel data 1

2            Unrel data 2

b) the second database has three fields, the primary key fromthe above database, the date, and comments, so for example, entries would be 

SNo   Date         Comments

1        10Sep       I have a pen

1        11Sep       he has a pen

1        12Sep       we have a pen

2        11Sep       they have a pen

2        12Sep       she has a pen

What i want to do is, in the first table, i want to take comments in from the second table BUT i want only the latest 2 comments AND i want to have the comments within one singe cell,

SNo.      Unrelated data            Date         Comments

1            Unrel data 1               12Sep       we have a pen

                                                 11Sep       he has a pen

2            Unrel data 2               12Sep       she has a pen

                                                 11Sep       they have a pen

Please note that the date and comments for each SNo, is fitted into just one cell each. How do i do this? I have not been able to figure this one out. 

[v-juanli-msft · Accepted solution]

Hi @jp278 

Create measures

measure date = MAX('Table 2'[Date])

Measure rank = IF([measure date]<>BLANK(),RANKX(ALLEXCEPT('Table 2','Table 2'[SNo]),[measure date],,DESC,Dense))

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jp278]

Have i not worded the question well enough? perhaps i need to redo this? Could someone help? I am new to this community. thanks.