Forum Discussion

shippen70's avatar
shippen70
Icon for Helper I rankHelper 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 duplic...
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/

amitchandak's avatar
amitchandak
Icon for Super User rankSuper 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
    Icon for Helper I rankHelper 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
      Icon for Microsoft Employee rankMicrosoft 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

      • shippen70's avatar
        shippen70
        Icon for Helper I rankHelper I
        6 years ago

        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