Filter between Dimension Tables | CALCULATE function Trick!

Here is a nifty CALCULATE function trick to filter between dimension tables or apply a reverse filter from a fact to a dimension table.
Enjoy!

- - - - My Courses - - - -
✔️ Mastering DAX in Power BI -

✔️ Power Query Course-

✔️ Master Excel Step by Step-

✔️ Business Intelligence Dashboards-

- - - - Blog - - - -

---
Artist Attribution
Music By: "After The Fall"
Track Name: "Tears Of Gaia"
Published by: Chill Out Records
Official After The Fall YouTube Channel Below
License: Creative Commons Attribution-ShareAlike 4.0 International (CC BY-SA 4.0)

[magansachdeva]

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)?

[wayneedmondson]

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

[fildude]

very nice and straightforward! thank you!

[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.

[msoffice]

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

[aliaziz]

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?

[markhenderson]

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.

[ahmedJaber]

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?

[radon]

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

[teigenxayden]

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

[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?

[aijaz]

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?

[aryansena]

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

[mitultank]

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

[ankitmonga]

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?

[anindadatta]

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)

[vrk]

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

[sahrashshaqeeb]

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 :)

[noahhadro]

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

[MrRohanroxx]

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 ?