topic Re: Remove duplicates and count from date column in new table in DAX Commands and Tips

Remove duplicates and count from date column in new table

Jay2022 — Thu, 07 Nov 2024 10:21:20 GMT

I have selected a dates column from another table and i now have them in a new column

Select Col =
SELECTCOLUMNS('Table')
,"Dates", 'Table'[Dates column])

I have then changed the format of the dates to MMM,Year

so i have

Jan24

Feb 24

Mar 24

etc

I now want to

1 - remove duplicates

2- count the number of occurances for each month eg

Jan 24 - 5

Feb 24 - 2 etc

Do I do as above and count the occurances from the Dates Column from th eoriginal table or do i add another column to this table the same as Dates only without the duplciates and then count from the table containing the duplicates.

so it woudl look like

Dates, Date_excluding_duplicates, Count

Any help really apprecaited

Re: Remove duplicates and count from date column in new table

bhanu_gautam — Thu, 07 Nov 2024 10:41:07 GMT

Jay2022 , Use below steps

Create a new table with unique dates and their counts:
UniqueDatesWithCount =
SUMMARIZE(
'Table',
'Table'[Dates column],
"Count", COUNT('Table'[Dates column])
)

Format the dates to "MMM, Year":
FormattedDatesWithCount =
ADDCOLUMNS(
UniqueDatesWithCount,
"FormattedDate", FORMAT('Table'[Dates column], "MMM yy")
)

Remove duplicates and count occurrences:
FinalTable =
SUMMARIZE(
FormattedDatesWithCount,
[FormattedDate],
"Count", SUM([Count])
)
This will give you a table with the formatted dates and their counts without duplicates.

Re: Remove duplicates and count from date column in new table

Jay2022 — Thu, 07 Nov 2024 14:06:21 GMT

Thanks first bit works fine but getting stuck on this bit

Format the dates to "MMM, Year":
FormattedDatesWithCount =
ADDCOLUMNS(
UniqueDatesWithCount,
"FormattedDate", FORMAT('Table'[Dates column], "MMM yy")
)

after FORMAT shoudl it be (UniqueDateswithCount'[Dates colum] basically from the new column i created

Re: Remove duplicates and count from date column in new table

Jay2022 — Thu, 07 Nov 2024 14:09:21 GMT

i get the following error The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Re: Remove duplicates and count from date column in new table

Jay2022 — Thu, 07 Nov 2024 14:46:15 GMT

are these each 3 seperate tables ?

Re: Remove duplicates and count from date column in new table

Anonymous — Fri, 08 Nov 2024 02:27:50 GMT

Hi Jay2022 ,

Thanks bhanu_gautam for the quick reply. I have some other thoughts to add:

(1) We can create a new table.

Col = SELECTCOLUMNS('Table' ,"Dates", 'Table'[Date column])

(2) We can create columns on Col table.

MMM_Year = FORMAT([Dates],"MMM yy")

Count = COUNTROWS(FILTER('Col',[MMM_Year]=EARLIER('Col'[MMM_Year])))

(3) We can create a SUMMARIZE table.

SUMMARIZE Table = SUMMARIZE('Col',[MMM_Year],[Count])

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.