topic Re: Counting dimension's attribute distinct values in DAX Commands and Tips

Counting dimension's attribute distinct values

Anonymous — Tue, 14 Apr 2020 13:31:07 GMT

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:

Fornitori con fatturato =
VAR DatiFatturato = CALCULATETABLE('Acquisti';'Acquisti'[Flag fatturato]=1)

RETURN
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!

Re: Counting dimension's attribute distinct values

Greg_Deckler — Tue, 14 Apr 2020 13:39:50 GMT

Can you just switch the relationship to Both?

Re: Counting dimension's attribute distinct values

Anonymous — Tue, 14 Apr 2020 13:55:30 GMT

The relationship has to be mono-directional. The point is that I would excpect the second formula expression to be working only after setting the relationship type to 'both', while it's working properly even with the relationship set to 'single'...

Re: Counting dimension's attribute distinct values

Anonymous — Wed, 15 Apr 2020 12:42:13 GMT

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

Re: Counting dimension's attribute distinct values

Anonymous — Wed, 15 Apr 2020 13:00:50 GMT

I'm not. I'm doing DISTINCTCOUNT( Dimension[DimAttribute] ), over a single-way relationship, and it's working the same as the CROSSFILTER() version of the formula.

Re: Counting dimension's attribute distinct values

Anonymous — Wed, 15 Apr 2020 13:03:30 GMT

In this case (which I missed) expanded tables are at work.

Best
D

Re: Counting dimension's attribute distinct values

Anonymous — Wed, 15 Apr 2020 13:04:47 GMT

But you should not use this technique as it can be slow and resource intensive. Use what I did above.

Best
D

Re: Counting dimension's attribute distinct values

Anonymous — Wed, 15 Apr 2020 13:22:47 GMT

Ok, but you may have to count the distinct number of dim attributes that don't have the same granularity of the dimension's key, e.g. the supplier's country. The point is that filter propagation in DAX is still not entirely clear to me.

Thanks

Bye!

Re: Counting dimension's attribute distinct values

Mariusz — Wed, 15 Apr 2020 13:37:37 GMT

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

Re: Counting dimension's attribute distinct values

AlbertoFerrari — Wed, 15 Apr 2020 13:46:09 GMT

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.

Re: Counting dimension's attribute distinct values

Anonymous — Wed, 15 Apr 2020 14:03:31 GMT

AlbertoFerrari, would a simple DISTINCTCOUNT( Fact[DimKey] ) not be enough?

Best
D

Re: Counting dimension's attribute distinct values

AlbertoFerrari — Wed, 15 Apr 2020 14:13:05 GMT

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 🙂

Re: Counting dimension's attribute distinct values

Anonymous — Wed, 15 Apr 2020 14:19:15 GMT

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

Re: Counting dimension's attribute distinct values

AlbertoFerrari — Wed, 15 Apr 2020 14:24:31 GMT

Yep, you're right. Looked to me that the goal was understanding what's happening underneath, and not just to make the measure work.

Anyway, good chat, this is what really matters. Apparently, there was no problem, but we had a good time discussing it 🙂

Re: Counting dimension's attribute distinct values

Mariusz — Wed, 15 Apr 2020 14:26:31 GMT

Hi AlbertoFerrari

Thanks for jumping in and clarifying.

Thanks

Mariusz

Re: Counting dimension's attribute distinct values

Anonymous — Wed, 15 Apr 2020 14:31:26 GMT

Yes, maybe the example was misleading, but the purpose was to understand a little bit more what happens behind the curtain...

Thank you all guys!