Forum Discussion
Counting dimension's attribute distinct values
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.
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.
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
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.
Only if you are counting the keys. If an attribute of the dimension is not unique (think at city, name, gender), then you need to use either SUMMARIZE or the expanded table. Or Bidirectional filtering... but you know, bidirectional cross-filter is like opening the door of Hell to say "Hello". Fascinating... but better stay away 🙂
- I know all of this. But the original task was to calculate the number of distinct suppliers that made at least one purchase.
Best
D