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

[ssbagley]

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??

[PVO3]

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

[ssbagley]

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]

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

[ssbagley]

That doesn't work, but I figured it out anyway.  Thanks!

[mattjford]

usually you publish how you solved it

[PVO3]

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

[ssbagley]

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!