Forum Discussion
Counting dimension's attribute distinct values
Hi guys,
I need your help. I have to count the distinct suppliers from witch I made at least a purchase, given a certain filter context.
Since the relationship between the dimension table of the suppliers ('Fornitori intestatari') and the fact table of the purchases ('Acquisti') is set as a standard one-way filter direction, one-to-many:
I was pretty sure that a dax formula like this one was necessary, levereging a CROSSFILTER() function or similar to obtain the distinct count of suppliers, filtered by the purchases fact table:
Fornitori con fatturato =
VAR DatiFatturato = CALCULATETABLE('Acquisti';'Acquisti'[Flag fatturato]=1)
RETURN
COUNTROWS(
CALCULATETABLE (
VALUES('Fornitori intestatari'[Fornitore - Descrizione]);
DatiFatturato;
CROSSFILTER ('Acquisti'[K_CodCli]; 'Fornitori intestatari'[K_CodCli]; BOTH)
))
and it works, indeed.
What I can't explain to myself is that this ultra simplified version of the expression seems to be working fine, as well:
VAR DatiFatturato = CALCULATETABLE('Acquisti';'Acquisti'[Flag fatturato]=1)
CALCULATE(DISTINCTCOUNT('Fornitori intestatari'[Fornitore - Descrizione]); DatiFatturato)
Can anyone explain me why? My hypothesis is that the DISTINCTCOUNT is working on what here (https://www.sqlbi.com/articles/expanded-tables-in-dax/) is called 'the expanded version' of the fact table 'Acquisti', instead of the 'physical' dimension table 'Fornitori intestatari', that is to say a sort of SELECT * FROM 'Aquisti' LEFT JOIN 'Fornitori intestatari', if I correctly understood.
If so, is this syntax safe, or is the first one better?
Thanks!
Hi guys,
The expanded version of Acquisti actually contains the full Fornitori table.
Therefore, if you use Acquisti as a filter argument in CALCULATE, you are filtering Fornitori too. CROSSFILTER is useless in this case. You could obtain the same performing a DISTINCTCOUNT, use CROSSFILTER avoiding the variable at all.
With that said, I would use something easier to read, like:
COUNTROWS ( SUMMARIZE ( Acquisti, Fornitori[Fornitore - Descrizione] ) )
Did not check performance, but it shouldn't be too bad, and definitely easier to read.
15 Replies
- Can you just switch the relationship to Both?
Hi Anonymous
The reason for
VAR DatiFatturato = CALCULATETABLE('Acquisti';'Acquisti'[Flag fatturato]=1)not being able to propagate the filters to 'Fornitori intestatari' is that 'Acquisti' within CALCULATETABLE is not expanded any more so it does not include 'Fornitori intestatari' in it.
You can try the below if the granularity of 'Fornitori intestatari'[Fornitore - Descrizione] is different to 'Fornitori intestatari'[K_CodCli]
Fornitori con fatturato = CALCULATE( DISTINCTCOUNT( 'Fornitori intestatari'[Fornitore - Descrizione] ); CROSSFILTER( 'Acquisti'[K_CodCli]; 'Fornitori intestatari'[K_CodCli]; BOTH ); 'Acquisti'[Flag fatturato] = 1 )AlbertoFerrari Please correct if I'm wrong, or elaborate so we can understand.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn
- It's rather simple. When you do distinctcount( Fact[DimKey] ), you are just calculating the number of different DimKey's in your fact table in the rows that are visible in the current context. No magic. This simple measure applied to your model does exactly what you want:
I have to count the distinct suppliers from witch I made at least a purchase, given a certain filter context.
Best
D
