Counting distinct values within a table

[TimoKytta]

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

[amitchandak · Accepted solution]

@TimoKytta , try a new column like

REFcount =
CALCULATE (
DISTINCTCOUNT ( DailyCapa2[WORKCENTER] ),
FILTER ( DailyCapa2, [REFID] = earlier([REFID])))

[smpa01 · Accepted solution]

@TimoKytta  you can write a measure like this

Measure = CALCULATE(DISTINCTCOUNT('tbl'[Workcenter]),ALLEXCEPT('tbl','tbl'[Refid]))

[TimoKytta]

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

[smpa01]

@TimoKytta  ALLEXCEPT is doing the <aggreagtion> based on the partition mention in ALLEXCEPT.

One thing to always remember with DAX - if you can create a measure don't create a column.

[amitchandak · Accepted solution]

@TimoKytta , try a new column like

REFcount =
CALCULATE (
DISTINCTCOUNT ( DailyCapa2[WORKCENTER] ),
FILTER ( DailyCapa2, [REFID] = earlier([REFID])))

[TimoKytta]

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!