Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a table lets call it Facts it has the following coulmns:
ProjectName Dep WorkerCode
X1 Sales 89
X1 Sales 63
X1 Admin 77
X1 Admin 99
X2 Purshase 01
I want a measure, a table, or a column where i can count total numer of workers groubed by Department and Project name, so the result is something like
ProjectName Dep Tot. NrOfWorkers
X1 Sales 2
X1 Admin 2
X2 purshase 1
I have tried the following but they are not working as I wished them to:
Column = CALCULATE(DISTINCTCOUNT(Facts[WorkerCode]),ALLEXCEPT(Facts,Facts[ProjectName],Facts[Dep]))
But its giving the right result
I have also tried to create a table from modeling:
Column = SUMMARIZE(Facts,Facts[ProjectName],Facts[Dep],"distinc",DISTINCTCOUNT(Facts[WorkerCode]))
But am getting an error saying the the expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Your help is highly apprecitaed 🙂
Solved! Go to Solution.
or if you want the DAX version:
Table = ADDCOLUMNS( SUMMARIZECOLUMNS( 'Fact'[ProjectName ], 'Fact'[Dep ] ), "Distinct Count", CALCULATE( DISTINCTCOUNT('Fact'[WorkerCode]) ) )
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").
Hi @Anonymous ,
Could you please mark the proper answers as solutions?
Best Regards,
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:
or if you want the DAX version:
Table = ADDCOLUMNS( SUMMARIZECOLUMNS( 'Fact'[ProjectName ], 'Fact'[Dep ] ), "Distinct Count", CALCULATE( DISTINCTCOUNT('Fact'[WorkerCode]) ) )
It worked in my case.. many thanks for that. 🙂
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?
Be sure you are using New Table, and not New Measure or New Column
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |