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/2022 | Shirt |
| 126 | 01/08/2022 | Pants |
I just want to create a new table to summarize the number of casenum's distinct count of the table above that is able to be filtered by other columns like Type or Date.
| DistinctCOuntColumn |
| 5 |
Thank you for your assistance.
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.
5 Replies
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.
