Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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!
Solved! Go to Solution.
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.
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.
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!
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'...
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.
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 🙂
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 🙂
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!
User | Count |
---|---|
42 | |
26 | |
21 | |
16 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |