Forum Discussion

neil37's avatar
neil37
Advocate I
3 years ago
Solved

Summary Table with Simple Distinct Count

Hello,    My request is rather simple.    My data is simply set up like below: Casenum Date Type 123 01/01/2022 Vest 123 01/01/2022 Shoes 124 01/03/2022 Hat 125 01/08/202...
Need Help
  • vicky_'s avatar
    vicky_
    3 years ago

    I think there's 3 options that might work for you:

    1. If you just need a distinct count, you could always create a measure with DISTINCTCOUNT(Casenum).
    2. But if you need a new table, you can go back into PowerQuery then duplicate the table, go to Transform tab > Count Rows > To Table and load that into your data model
    3. Click on Table Tools > New Table and use something like Table = SUMMARIZE('Table', "DistinctCountColumn", DISTINCTCOUNT('Table'[Column1])) to create your summary table.
neil37's avatar
neil37
Advocate I
3 years ago

Thank you vicky_  what expression would be used after I name the second column if I just wanted it to list the Type of clothes (from the example)?

SummaryTable = SUMMARIZE('Table', "column name", ???? ('table'[column1]),"DistinctCountColumn",DISTINCTCOUNT('table'[column2]))

 

vicky_'s avatar
vicky_
Super User
3 years ago

You may have already figured this out, but you will only need to provide a name for the new column if it's an aggregate column (e.g. COUNT, SUM, AVG... etc.). 
EDIT: I forgot to answer the question. Basically, you don't need an extra expression