Forum Discussion
neil37
Advocate I
Advocate ISummary 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
vicky_Super User
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.
- vicky_
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.
