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").
or if you want the DAX version:
Table =
ADDCOLUMNS(
SUMMARIZECOLUMNS(
'Fact'[ProjectName ],
'Fact'[Dep ]
),
"Distinct Count",
CALCULATE(
DISTINCTCOUNT('Fact'[WorkerCode])
)
)
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