Direct Query and creating a Headcount Column

[Anonymous]

Hi,

I'm a new user with Power BI and I'm using Direct Query.  I'm creating a set of monthly reports for a business HR Health, which would include gender, people by region, people by job level etc.  In the data set there isn't a field (Column) that has a unique value of "1" so that I can use this as my Headcount.  

I've used the formula 

HEADCOUNT = CALCULATE(DISTINCTCOUNT(combined[region]))

However this formula doesn't work in Direct Query.  Is there solution to this?

Thanks in advance.

Cheers

Simon

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Please create a measure instead of a columns using DAX below. DISTINCTCOUNT function only supported in measure in DirectQuery mode, see this article.

HEADCOUNT = DISTINCTCOUNT(combined[region])

Regards,
Lydia

[Anonymous]

Thank you Lydia.