Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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:

 

Immagine.png

 

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!

 

 

1 ACCEPTED 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.

Alberto Ferrari - SQLBI

View solution in original post

15 REPLIES 15
Anonymous
Not applicable

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
Anonymous
Not applicable

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.

Anonymous
Not applicable

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

Best
D
Anonymous
Not applicable

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

Best
D
Anonymous
Not applicable

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!

 

 

Greg_Deckler
Super User
Super User

Can you just switch the relationship to Both?

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

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.

Alberto Ferrari - SQLBI
Anonymous
Not applicable

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

Best
D

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 🙂

Alberto Ferrari - SQLBI
Anonymous
Not applicable

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

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 🙂

Alberto Ferrari - SQLBI
Anonymous
Not applicable

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!

Hi @AlbertoFerrari 

 

Thanks for jumping in and clarifying.

 

Thanks

Mariusz

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors