Get Value corresponding to max date over Primary Key

[Anonymous]

Hello Team,

I am fairly new to Power BI and I am stuck with a logic where i have to Get the value from a column based on max date of each request.

My Tables consist of a Request table connected to a Comments table. Both tables are connected with each other by a One to Many relationship, The request table has ID column as Primary key and comments table has the relevant comments for the ID.

I want to display the latest comments for each ID in a Table Box.

Thank you,

Siddhesh H Mane.

[v-juanli-msft]

Hi @Anonymous 

Does two tables look like this?

Request table

ID

1

2

3

Comments table

ID	date	comment
1	5/1/2019	comment11
1	5/2/2019	comment12
1	5/3/2019	comment13
2	5/1/2019	comment21
2	5/2/2019	comment22
2	5/3/2019	comment23
3	5/1/2019	comment31
3	5/2/2019	comment32
3	5/3/2019	comment33

Request table (relationship: one to many) Comments table

Finally, the output should be as follows:

Get the value from a column based on max date of each request

ID	date	comment
1	5/3/2019	comment13
2	5/3/2019	comment23
3	5/3/2019	comment33

Is my understanding correct?

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.

[Anonymous]

Hi @v-juanli-msft ,

Thank you for helping me out. Yes this is how the output should be.

Thank you,

Siddhesh H Mane.

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Create two measures in comments table,

Max date = CALCULATE(MAX('Comments table'[date]),ALLEXCEPT('Comments table','Comments table'[ID]))

flag = IF(MAX('Comments table'[date])=[Max date],1,0)

Add [flag] in the visual level filter

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.

[Anonymous]

Hi @v-juanli-msft ,

Thanks a lot, will definitely try this one out. I had tried using the below DAX to display the output for the time being

Latest Comments = MAXX(FILTER(RequestComments, RequestComments[RequestId] && RequestComments[Created] = MAX(RequestComments[Created])), RequestComments[Comments])

I don't know exactly if this is the right approach, but i was getting the desired output.

Please let me know if this is correct.

Thanks a lot,

Siddhesh H Mane.