Filter between Dimension Tables | CALCULATE function Trick!

[@magansachdeva8488]

Amazing video, Thanks for sharing. Just a quick question in terms of Performance - It is said - to not use a table as filter, Can we optimize this by using one column filter (key column)?

[@fildude]

very nice and straightforward! thank you!

[@markhenderson3771]

Thank you! I cannot seem to apply your example to mine. I have two dimension tables: Owners and Customers, and one Fact table: Transactions. Customers is related to Transactions by Customer Number. (Each Customer has an Owner. Owners can own many customers.) I am trying to count the number of Owners whose Customers have at least one transaction on the Transactions table.

[@wayneedmondson1065]

Awesome Chandeep! Thanks for this interesting lesson. Thumbs up!!

[@BISimplifier]

Good One. The trick here is even though you filter by entire sales table DAX engine internally convert this only for filter by customer key in sales table which filters by unique customers.

[@Wzxxx]

Really?

[@asad512]

Lots of thanks for your support

[@teigenxayden]

IS the usage of expanded table to solve the reverse filter?

[@muhammadsadiq2330]

is your course available in Hindi? plz, share course details and price.

[@sashawhittaker6141]

well done, well explained, thanks!

[@GoodlyChandeep]

Glad you enjoyed it!

[@radon8452]

Can I just set all the relationships in both directions in the diagram view? Is it a good idea? Its working fine

[@mitultank7872]

From where you get the data.? Please share the link

[@aijaz4441]

Hi Chandeep. I am still not able to understand how the DAX formula works. How are the duplicates not accounted for in the correct formula?

[@ahmed007Jaber]

thank you for this. i have a table that contains a sequence from 1 to 10. this table isn't linked to any other table and those values aren't references in any table i want to use this so that i can add it in a filter and based on my choice my measures calculation would change. in so far it seems it doesn't work. any ideas how to make it work? just add it to any measure and say * but the input of that table. what do you think?

[@mr.k2430]

Excellent!

[@hamnaanwar3651]

just what i was looking for, thanks!

[@GoodlyChandeep]

Glad I could help!

[@sarveshdeshpande1772]

Can’t we use crossfilter dax ??

[@GoodlyChandeep]

yes we can

[@aryansena7290]

Hi, Can we get a workaround that measure can be used as page level filter. Thanks in advance.

[@pankajroshan]

What if, we have to create a simple visual where get all customer who are active in year =2005. Since in this case we don’t need to create any measure how we will achieve it and time dimension and customer table are not linked. Can you help?

[@msoffice6037]

Perfect video! but in case if you would like to know - the way you pronounce Year word in English language actually means body part (parts that listen ear) just decided to say it in order to help you improve

[@aliaziz2461]

hi, awsome technique which is still i still never read even in books. one thing i want to ask is this measure show values after applying RLS?

[@hamidbahari6588]

Thanks For Sharing.

[@vrk5710]

How do you display all rows side by side in a matrix visual without using Index column?

[@noahhadro8213]

is this faster than using something light "treatas" on just a specific column"

[@ankitmonga4318]

Hey, I just tried to open the sample file, however, the link took me on some invalid side where I could not download it. Could you please suggest the way to get it?

[@kennethstephani692]

Great video!

[@joanna9348]

Amazing, as always :)

[@anindadatta164]

could you create a real table of unique customers by "value" function, and then do sumx on the same by relating it with customers(random salary)

[@sujit3375]

Please show us how fact table filters dim table in background

[@grahamlindsay9798]

Excellent.

[@GoodlyChandeep]

Thanks 😊

[@sahrashshaqeeb9400]

Hi Brother, suppose i want account names that have NOT responded in the last 30 days? or say I want names of accounts that have not been active in last 30 days. How to get that data .. pls help Goodly :)

[@FRANKWHITE1996]

Thanks for sharing! 👍

[@ramanhakimipour4522]

If we replace averagex with sumx it should fix incorrect one

[@JisJ91]

Great why don’t you start Crete a Wtsup group for direct interaction

[@MrRohanroxx1]

I just had one doubt With your total sales correct measure After the filter context is applied won't the sales table still have duplicate customer keys ? Because in dax studio you used values to remove the duplicates So how is measure functioning correctly then ?

[@yuqinghuang5891]

Hi there! I have same question. Did you figure it out? thanks.

[@KgasS]

The concept behind this is expanded table...

[@idonjohnson6999]

So using the obvious/easiest/shortcut....set relation crossfilter to both and a filter on the fact table, filters all the dimensions that are also set both.

[@marounsader318]

SHIT you kidding! how this was never mentioned in any documentation! thanks a lot!

[@GoodlyChandeep]

Glad it was helpful!

[@marounsader318]

@GoodlyChandeep please if you can make more use cases like ranking or market share using this method, would be grateful

[@AgulloBernat]

So you don't need to use CROSSFILTER function?? That's odd!

[@vijayaragavankrishnamoorth6155]

Hi Bernat, I also think, that why we are not using cross filter ? Have you tried this calculation with cross filter ? Is it giving same result.

[@vijayaragavankrishnamoorth6155]

@bernat agullo

[@SUDHIRKUMAR-cr9zs]

Thanks a lot Sir....... But you explain very fast...

[@GoodlyChandeep]

💚

[@EdwinVeenboer]

In your correct measure, you dont specify you need unique customers salary. How is it not giving the same result as your wrong dax?

[@janwillemvanholst]

The definitive guide to DAX 2nd edition page 143: 'you should avoid table filters because they usually are more expensive'

[@GoodlyChandeep]

Hi Jan, this isn't same as iterating each row of the Sales table over a FILTER Function. I am just leveraging the relationship between the Customers and Sales Table by this trick. This should clarify it further - www.daxpatterns.com/related-distinct-count-excel-2013/

[@janwillemvanholst]

@@GoodlyChandeep Thanks. I will have a look.

[@TheCraigie007]

@@janwillemvanholst I think here we are looking here at 'Expanded Tables'. A core DAX concept which Alberto & Marco explore in the Definitive Guide.

[@janwillemvanholst]

@@GoodlyChandeep I used the wrong quote. It should have been page 448 '...it is better to avoid using table filters in CALCULATE statements,...'