Forum Discussion
SUMMARIZE USING DISTINCTCOUNT
Hello, all!
I need help with the following issue:
I have this table:
| Year | Month | ID | Text |
2021 | Jan | 123 | A |
| 2021 | Feb | 123 | A |
| 2021 | Mar | 123 | A |
| 2021 | Mar | 123 | B |
| 2021 | Mar | 123 | C |
I need to do a distinct count to see how many Texts i have for Year/Month/ID.
So I need the following output
| Year | Month | ID | Text |
| 2021 | Jan | 123 | 1 |
| 2021 | Feb | 123 | 1 |
| 2021 | Mar | 123 | 3 |
I've tried something like this
test = (DISTINCTCOUNT(
SUMMARIZE ( Consult, Consult[date].[year], Consult[date].[month], Consult[id]),
[Text])
But it didn't work 😞
I believe SUMMARIZE can solve the poblem but if there is any other option, please, Im all ears.
So, if anyone coulp help me, Ill be plesead 🙂
Anonymous Oh, sorry, should just be this:
test = COUNTROWS( SUMMARIZE ( Consult, Consult[date].[year], Consult[date].[month], Consult[id]) )
3 Replies
Anonymous Try:
test = (COUNTROWS( SUMMARIZE ( Consult, Consult[date].[year], Consult[date].[month], Consult[id]), [Text])SUMMARIZE will already return each row as a distinct combination of what you are summarizing over.
Anonymous Oh, sorry, should just be this:
test = COUNTROWS( SUMMARIZE ( Consult, Consult[date].[year], Consult[date].[month], Consult[id]) )