Forum Discussion
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...
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.
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