Forum Discussion

shippen70's avatar
shippen70
Helper I
6 years ago
Solved

Summarize Table and Remove Duplicates

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')
)
duplicate
SUMMARIZE
  • amitchandak's avatar
    amitchandak
    6 years ago

    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/

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      shippen70
      Helper I
      6 years ago

      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's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        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