How to create Distinct Column Count using DirectQuery?

[Anonymous]

Dept. Name	Office Location	Desk Type	Count Desk Type By Dept. Name.
Finance	Left-Side Office Dept.	Complex	2
Finance	Left-Side Office Dept.	Basic	2
Finance	Right-Side Office Dept.	Complex	2
HR	First Floor	L-Shape	1
HR	Second Floor	L-Shape	1
IT	Third Floor	U-Shape	3
IT	Fourth Floor	Basic	3
IT	Fifth Floor	Complex	3
IT	Sixth Floor	U-Shape	3

The goal is to create the calculated column “Count Desk Type by Dept. Name”.

This column is built off of the distinct count of desk type for each dept. name.

How would we accomplish this in Power BI?

I must create this Column in the Query Editor using Power Query.

NOTE: This is for DirectQuery. It is NOT possible to do,
“Add Column” in DAX.

Count Desk Type by Office Name =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Desk Type] ),
ALLEXCEPT ( 'Table', 'Table'[Office Name] )
)

CALCULATE IS NOT SUPPORTED BY DAX DirectQuery.

[amitchandak]

@Anonymous , You should able to create this as a measure like that. I doubt you can create a complex column like that in direct query.

new measure =CALCULATE (
DISTINCTCOUNT ( 'Table'[Desk Type] ),
ALLEXCEPT ( 'Table', 'Table'[Office Name] )
)

[Anonymous]

@amitchandak  

See the above image. This is not possible when using DirectQuery.
It would appear that what I am trying to do is Impossible for DirectQuery.
In BOTH, DAX or Power Query.

[V-lianl-msft]

Hi @Anonymous ,

Please create a measure not column:

Measure =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Desk Type] ),
ALLEXCEPT ( 'Table', 'Table'[Office Name] )
)

Or you can try using power query.

Please refer this thread:

https://community.powerbi.com/t5/Desktop/count-distinct-on-column-in-power-query/m-p/165494 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.