Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Distinctcount GroupBy?

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 :) 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    or if you want the DAX version:

    Table = 
ADDCOLUMNS(
    SUMMARIZECOLUMNS( 
        'Fact'[ProjectName ], 
        'Fact'[Dep      ]
    ),
    "Distinct Count", 
    CALCULATE(
        DISTINCTCOUNT('Fact'[WorkerCode])
    )
)

  • MarkLaf's avatar
    MarkLaf
    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").

     

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    if you are not oppose to using Power Query, this can be done with just a few clicks:

     

    1. Have your table loaded
    2. Reference that query (this will create a new query)
      1. Go to Transform--> Table--> GroupBy 
      2. Then use these inputs:
    3. And your final table:

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      or if you want the DAX version:

      Table = 
ADDCOLUMNS(
    SUMMARIZECOLUMNS( 
        'Fact'[ProjectName ], 
        'Fact'[Dep      ]
    ),
    "Distinct Count", 
    CALCULATE(
        DISTINCTCOUNT('Fact'[WorkerCode])
    )
)

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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? 

  • MarkLaf's avatar
    MarkLaf
    Super User
    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").

     

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous ,

     

    Could you please mark the proper answers as solutions?

     

     

    Best Regards,