Forum Discussion
Counting distinct values within a table
Hello y'all
I'm trying to build a calculation to get the following result. I've earlier managed to to some similar with instructions found here, but for some reason I don't get this work right.
The result table of calculation with the needed column X should look like this:
| Workcenter | Day | Refid | X |
| AAA | 20220111 | 5 | 2 |
| AAA | 20220112 | 5 | 2 |
| AAA | 20220113 | 5 | 2 |
| AAA | 20220114 | 5 | 2 |
| AAA | 20220115 | 5 | 2 |
| BBB | 20220111 | 5 | 2 |
| BBB | 20220112 | 5 | 2 |
| BBB | 20220113 | 5 | 2 |
| BBB | 20220114 | 5 | 2 |
| BBB | 20220115 | 5 | 2 |
| CCC | 20220111 | 1 | |
| CCC | 20220112 | 1 | |
| CCC | 20220113 | 1 | |
| CCC | 20220114 | 1 | |
| CCC | 20220115 | 1 | |
| DDD | 20220111 | 7 | 1 |
| DDD | 20220112 | 7 | 1 |
| DDD | 20220113 | 7 | 1 |
| DDD | 20220114 | 7 | 1 |
| DDD | 20220115 | 7 | 1 |
Column X should show how many distinct work centers are in that table for that row's REFID-value.
For example refid = 5 has two (AAA and BBB). Refid = 7 has just one (= DDD) and since the table can also have empty refids, those could be 1.
I tried in vain something like this:
REFcount =
CALCULATE (
DISTINCTCOUNT ( DailyCapa2[WORKCENTER] ),
FILTER ( DailyCapa2, [REFID] = DailyCapa2[REFID] ))
But I can already see that the filtering condition doesn't make sense - I just don't know what to use there...
TIA
Timo
TimoKytta , try a new column like
REFcount =
CALCULATE (
DISTINCTCOUNT ( DailyCapa2[WORKCENTER] ),
FILTER ( DailyCapa2, [REFID] = earlier([REFID])))
5 Replies
- amitchandak
Super User
TimoKytta , try a new column like
REFcount =
CALCULATE (
DISTINCTCOUNT ( DailyCapa2[WORKCENTER] ),
FILTER ( DailyCapa2, [REFID] = earlier([REFID])))Thanks for your quick help! 🙏🏻
I actually had that EARLIER() in other similar filter condition, but didn't realize it works in this case too. I read again the description of that function and started to understand a bit of it's logic. So it gives the current value instead of 'previous value' as I have thought before.
Thanks for this!
Thanks to you too!
I chose to use this filtering style since my table has a bit over 730 000 rows and that earlier-function had a mention about possible performance issues with big tables.
Why this your solution works is a bit bigger mystery to me still! ^_^
Does it do so that that the column mentioned in ALLEXCEPT-function is taken as a current rows value for each row? The function documentation says that it's: 'The column for which context filters must be preserved.'
