Summarize Table and Remove Duplicates

[shippen70]

I am working with a table of data that I am trying to summarize.  For development I am using New Table and Summarizing.  I get the first level of summary to work but my table ends up with some duplicate values.  Ultimately I want to count the rows that remain after summarizing and removing the duplicate.  My Row Count should be 1 for every row.  The other option would be to replace the values that are greater than "1" with "1".  Any thoughts how I can remove these duplicates without having to setup the table in Power Query?

Operation Count by Day =

SUMMARIZE('_AELaborEdit - Clock In Date',

'_AELaborEdit - Clock In Date'[ClockInDate],

'_AELaborEdit - Clock In Date'[Job],

'_AELaborEdit - Clock In Date'[Operation],

"Operations Count",

COUNTROWS('_AELaborEdit - Clock In Date')

)

[amitchandak · Accepted solution]

@shippen70 .

Try distinct(Table)

or in case summarize group by what you distinct first and take measure with rename post that.

You can use table inplace of summarize

countrows(

SUMMARIZE('_AELaborEdit - Clock In Date',
'_AELaborEdit - Clock In Date'[ClockInDate],
'_AELaborEdit - Clock In Date'[Job],
'_AELaborEdit - Clock In Date'[Operation],
"Operations Count",
COUNTROWS('_AELaborEdit - Clock In Date'))

or

countx(

SUMMARIZE('_AELaborEdit - Clock In Date',
'_AELaborEdit - Clock In Date'[ClockInDate],
'_AELaborEdit - Clock In Date'[Job],
'_AELaborEdit - Clock In Date'[Operation],
"Operations Count",
COUNTROWS('_AELaborEdit - Clock In Date')
)
), [Operations Count])

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[shippen70]

Thanks for the reply.  When I try to add the COUNTROWS before the Summarize I get an error:

"The expression specified in the query is not a valid table expression"

When I try to use COUNTX it will not allow me to reference back to  the "Operations Count" Coulmn created in the summarize.

Any additional thoughts?

[mahoneypat]

I put the expression provided by @amitchandak into DaxFormatter.com.  It was just missing the last ")".

NewMeasure =
COUNTROWS (
SUMMARIZE (
'_AELaborEdit - Clock In Date',
'_AELaborEdit - Clock In Date'[ClockInDate],
'_AELaborEdit - Clock In Date'[Job],
'_AELaborEdit - Clock In Date'[Operation],
"Operations Count", COUNTROWS ( '_AELaborEdit - Clock In Date' )
)
)

Regards,

Pat

[shippen70]

I had corrected the braket issue but still get the same result.  Based on  @amitchandak input to countrows again  I just created a second Measure to count the table rows again.  I achived what I was trying to.  Thanks

[ryan_mayu]

@shippen70 

please share the sample date and the expected output.