Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
@cshepler , 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] )
)
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.
Hi @cshepler ,
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |