Forum Discussion

ssbagley's avatar
ssbagley
Helper III
3 years ago

Distinct Count (Direct Query) - Why is this so hard!?

Hi - me again.  Source data is employee expense submissions in a SQL table via Direct Query.  I want to count the number of expense reports by employee for a given date range (slicer).  I've tried:

= Countrows(Summarize(Expenses, Expenses[ReportNumber]) )

= DISTINCTCOUNT (Expenses[ReportNumber] )

= COUNTROWS(DISTINCT(SELECTCOLUMNS(FILTER(Expenses,Expenses[ReportNumber]<>0),"Reports",Expenses[ReportNumber])))

 

I know this has to be possible -- what am I doing wrong??

Need Help

7 Replies

  • PVO3's avatar
    PVO3
    Impactful Individual
    3 years ago

    COUNTROWS(ALL('Table'[Primairy key])) is working fine for me

    • ssbagley's avatar
      ssbagley
      Helper III
      3 years ago

      This displayed the same value for every employee which is not correct, but it did return a result which is an improvement from my previous attempts!

  • PVO3's avatar
    PVO3
    Impactful Individual
    3 years ago

    Im sorry. Instead of primairy key i meant your name column. But im afraid this operation over this column has performance issues

    • ssbagley's avatar
      ssbagley
      Helper III
      3 years ago

      I don't want to countrows of the name column though because one name may have 500 unique expense reports.  I want to identify the number of expense reports per person (not number of expense submissions). 

      • PVO3's avatar
        PVO3
        Impactful Individual
        3 years ago

        So then do COUNTROWS(ALL(Expenses[ReportNumber]))