Forum Discussion
Distinctcount GroupBy?
- Anonymous7 years ago
or if you want the DAX version:
Table = ADDCOLUMNS( SUMMARIZECOLUMNS( 'Fact'[ProjectName ], 'Fact'[Dep ] ), "Distinct Count", CALCULATE( DISTINCTCOUNT('Fact'[WorkerCode]) ) ) - 7 years ago
I'll quickly note that if you're goal is to simply have a visual that shows this info in a report/dashboard, then you don't need to write any DAX.
Simply create a table visual, add in ProjectName, Dep, and WorkerCode, then hit dropdown on WorkerCode value and change aggregation setting to "Count (Distinct)" – you can change display name of the columns in the same dropdown menu, too (if for example you don't like "Count of WorkerCode").
if you are not oppose to using Power Query, this can be done with just a few clicks:
- Have your table loaded
- Reference that query (this will create a new query)
- Go to Transform--> Table--> GroupBy
- Then use these inputs:
- And your final table:
- Anonymous7 years agoNot applicable
or if you want the DAX version:
Table = ADDCOLUMNS( SUMMARIZECOLUMNS( 'Fact'[ProjectName ], 'Fact'[Dep ] ), "Distinct Count", CALCULATE( DISTINCTCOUNT('Fact'[WorkerCode]) ) )- Anonymous7 years agoNot applicable
Many thanks for your answer. However, Iam still getting the following when using DAX: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value"
Any suggestions?
- Anonymous7 years agoNot applicable
Be sure you are using New Table, and not New Measure or New Column
- Anonymous7 years agoNot applicable
It worked in my case.. many thanks for that. :)