Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
Proud to be a Datanaut!
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
@Anonymous
Yes, that's it 🙂
Pete
Proud to be a Datanaut!
@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
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
Proud to be a Datanaut!
@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?
@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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |