Summary Table with Simple Distinct Count

[neil37]

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. 

[vicky_ · Accepted solution]

I think there's 3 options that might work for you:

1. If you just need a distinct count, you could always create a measure with DISTINCTCOUNT(Casenum).
2. 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
3. Click on Table Tools > New Table and use something like Table = SUMMARIZE('Table', "DistinctCountColumn", DISTINCTCOUNT('Table'[Column1])) to create your summary table.

[neil37]

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!

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

[neil37]

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]))

[vicky_]

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