Forum Discussion
Summary Table with Simple Distinct Count
I think there's 3 options that might work for you:
- If you just need a distinct count, you could always create a measure with DISTINCTCOUNT(Casenum).
- 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
- Click on Table Tools > New Table and use something like Table = SUMMARIZE('Table', "DistinctCountColumn", DISTINCTCOUNT('Table'[Column1])) to create your summary table.
Hi vicky_ ,
Thank you for this solution - if I wanted to add a few columns from the original table as well in order to add relationships in the model and filter - what would have to add to that new table code? Thank you!
You can add the column name(s) between the 'Table' and "DistinctCountColumn".
e.g. if you wanted to add the Date, then it would be SUMMARIZE('Table', "Date", "DistinctCountColumn", DISTINCTCOUNT('Table'[Column1]))
which would return the number of distinct Casenums per date.
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]))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