Forum Discussion

Jay2022's avatar
Jay2022
Icon for Helper IV rankHelper IV
1 year ago
Solved

Remove duplicates and count from date column in new table

 

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

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

 

"COUNT"
"Dates"
"DAX " "DAX help" 'Need help'
"DAX Commands and Tips"
  • Anonymous's avatar
    Anonymous
    1 year ago

    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. 

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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. 

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    1 year ago

    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.

  • Jay2022's avatar
    Jay2022
    Icon for Helper IV rankHelper IV
    1 year ago

    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

  • Jay2022's avatar
    Jay2022
    Icon for Helper IV rankHelper IV
    1 year ago

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