topic Visual Showing Duplicate Records when I only want the max! in DAX Commands and Tips

Visual Showing Duplicate Records when I only want the max!

debenaire — Tue, 05 Dec 2023 11:16:42 GMT

Hi, I am trying to show the latest date for a given ID and show corresponding information for that record in a table visual. The dax works well but only when i show the ID. when i bring in other fields from the same table it duplictes the id as there may be different names. all i want to show is the record for the mx date. Can anyone help please i'ts driving me mad! Thanks.

My dax is:

Latest Status =

var Max_Date = MAX('Date Selection - Contacts'[Date])

return

CALCULATE(max('contacts'[CreatedOn]),

values(contacts'[DBName-Contact_Id]),

'contacts'[CreatedOn] <= Max_Date)

See attached screenshots of what shows with the DBName-Contact_Id alone versus brining in an additional field from the contact table. Thanks in advance!!

Re: Visual Showing Duplicate Records when I only want the max!

ray_codex — Tue, 05 Dec 2023 11:38:24 GMT

The filter context of your first visual has 2 names, so does a calculation on both names returning your last status calculation's result on both names. I think you can remove this with adding ALL(yourtable[name]) as a parameter to CALCULATE.

Re: Visual Showing Duplicate Records when I only want the max!

debenaire — Tue, 05 Dec 2023 11:46:28 GMT

thank you so do you mean removing the values part of the fliter? are you able to rewrite? i thought i may need this as i want to make sure there is only a value per dbname-id? thanks

Re: Visual Showing Duplicate Records when I only want the max!

ray_codex — Tue, 05 Dec 2023 12:18:24 GMT

Latest Status =

var Max_Date = MAX('Date Selection - Contacts'[Date])

return

CALCULATE(max('contacts'[CreatedOn]),

values(contacts'[DBName-Contact_Id]),

'contacts'[CreatedOn] <= Max_Date, ALL('contacts'[Name])
)

Re: Visual Showing Duplicate Records when I only want the max!

debenaire — Tue, 05 Dec 2023 13:02:10 GMT

thanks but values still show