HELP with expanded table in DAX

[Anonymous]

Hi Team,

Need your expertise to understand the logic behind expanded/Virtual tables which are created in the temporary memory for DAX calculation

I referred the link https://www.sqlbi.com/articles/expanded-tables-in-dax/ and i have few doubts 

1) does the expansion happen based on filtering direction?

2) Can you please explain how does the expanded table of Sales has product & Topsellerproduct? why didnt the expanded table of Product have sales data??

Regards,

Husna

[Anonymous]

Hi @BA_Pete 

Can you correct my understanding as per your explaination 

Product table expanded version is Product itself

Purchase table will have columns from products and Centre

Centre table expanded version is Centre table itself

[BA_Pete]

@Anonymous 

Yes, that's it 🙂

Pete

[Anonymous]

@BA_Pete 
So does it mean that if i have a slicer of Product and another slicer of purchase and if i select any of product ,it will not filter purchase?

Please correct me if im wrong

[BA_Pete]

This doesn't sound correct. This is getting away from cardinality now, into filter direction.

A slicer with values from Products WILL filter Purchase, but will not filter Centre.

A slicer with values from Centre WILL filter Purchase, but will not filter Products.

A slicer with values from Purchase will NOT filter either of the other tables.

This behaviour can be adjusted by changing the filter direction to BOTH on either of the relationships.

Pete

[Anonymous]

@BA_Pete Great thanks for your immense knowledge sharing

my last doubts probably

1)So when i write a measure for it to work correctly firstly expanded table gets created in temporary memory and later on filtering works  on the expanded table itself or on physical table?

2) If i make Product->Purchase relationship bi-directional, since we have centre id in purchase table even the centre table gets filtered?

[BA_Pete]

@Anonymous 

1) This is getting into the technical implementation of the concept which I'm not really sure about to be honest. Which table Power BI actually filters on behind the scenes is, I believe, largely irrelevant for standard deployments.

Basically, the expanded table concept helps you to understand which fields from which tables can be used together in visuals, and can be referenced in calculated columns and measures, without creating Cartesian Products (all rows > all rows crossjoin). You don't generally have to worry about it beyond that.

2) No. If you make Products->Purchase bi-directional, you will have this scenario:

A slicer with values from Products will filter Purchase, but will not filter Centre (as before).

A Slicer with values from Purchase will now filter Products, whereas it didn't before, but will still not filter Centre.

A slicer with values from Centre will filter Purchase, as it did before, but will now also filter Products.

The arrows on the relationship lines show you which way the filters will travel. For example, the single arrow from Centre to Purchase tells you that Centre can filter Purchase but, as there's not an arrow pointing the other way, Purchase cannot filter Centre.

When you make Products->Purchase bi-directional, you will see two arrows, one pointing each way - this means that each of these tables can now filter each other.

Filters will move all the way down relationships/tables where the arrows all point in that direction. This is why, in your bi-directional scenario, Centre will now filter Products as well as Purchase: The arrow on Centre->Purchase points in that direction so the filter travels down that way, but you have also created a new arrow on Purchase->Products by going bi-directional, so the filter will now continue all the way to Products.

Sorry if I've made this even more confusing now!

Pete

[BA_Pete · Accepted solution]

Hi @Anonymous 

1) No, not filter direction but cardinality: only columns on the 1 side (not many side) of a relationship are added to the source table to expand it.

2) As per point 1), TopSellerProduct is on the 1 side of Product, so Product expands to include TopSellerProduct. Product is on the 1 side of Sales, therefore Sales expands to include Product (which also already includes TopSellerProduct).

Pete