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