DAX CrossFilter Function in Power BI Write the Formula both directional but keep the relationship

If you are familiar with relationships in Power BI, you know that there are scenarios that you may need to change the direction of the relationship to a both-directional. A both-directional relationship comes at a cost, which is mainly the performance and ambiguity of the model. There is a way to write a calculation in a both-directional way but keep the relationship still single direction. This would help with the performance too because the performance impact will only happen when using this measure. In this article and video, I explain how you can do that.
download the Power BI file and get the links to study more from my blog article here:
radacad.com/da...
*******************
Want to learn about Power BI or AI? or take your Power BI and AI knowledge to the next level for building reports? Check out our courses in RADACAD Academy for all aspects of Power BI and AI.
RADACAD courses: radacad.academy
Our upcoming courses: learn.radacad....
Free books and tools for Power BI: radacad.com
Power BI Helper: powerbihelper....
Become an academy member: learn.radacad....
*******************
********
CONNECT with US!
********
-- / radacad_com
-- / rad_reza
-- / leila_etaati
-- / radacad
-- / leila-etaati-b0304636
-- / rezarad
-- / 6393399
-- radacad.com
********
#PowerBI #PowerBIfromRookieToRockStar #PowerQuery #DAX #Visualization #RADACAD

[@joelngige5776]

Great explanation on CrossFilter Function. I ahve finally figured out how it works.

[@Super123ok]

Always loved your video tutorials, well explained real use case.

[@pradeepkoppaka4340]

thank you, and love your teaching style.

[@RADACAD]

Glad you enjoy it!

[@ketanpatil3489]

excellent video on this functionality...thank you Reza 👏

[@sensational4459]

Wonderful explanation. Thanks and well done.

[@pabakdas5455]

Best explanation so far!!

[@Alireza-ih5vu]

Perfect, Thanks Reza

[@Search4Knowledge]

I love this so much, thank you! This gives me so much flexibility at function level.

[@sr5726]

@4.47 you mentioned OneWay_LeftFiltersRight /OneWay_RightFiltersLeft used for One to One. Microsoft doc says
OneWay_LeftFiltersRight - cannot be used with a one-to-one / many-to-one relationship.
OneWay_RightFiltersLeft - cannot be used with a one-to-one / many-to-one relationship.
Could you give examples where above two can be used.

[@davidgitau8825]

I noted on your crossfilter function that your column1 was on the once side of the relationship and then column 2 is the many side of relationship (Crossfilter(DimProduct[Productkey],factInternetSales[ProductKey], both). Crossfilter function expects column1 to be the manyside of relationship and column2 to be one side of relationship Crossfilter(factInternetSales[ProductKey], DimProduct[Productkey],both). Does interchanging like the way did have any expected result impact?

[@tolgakaptan6633]

SalesQuota measure isn't working properly. 95.714.000,00 at every row. How do you explain it?

[@ash21d]

Life saver!!!!

[@tolgakaptan6633]

FactResellerSales or FactSalesQuto tables have no CustomerKey detail. I can't understand the distribution logic: ResellerSalesAmount or SalesQuotaAmount by FullName. Can you please explain it?

[@EasyStreet88599]

thank you

[@RADACAD]

You're welcome

[@RaviKumar-gz9my]

Excellent. Appreciate

[@SergiyVakshul]

It does not really matter, but according to Docs the first argument of CROSSFILTER "represents the many side of the relationship to be used"

[@RADACAD]

Yes, it is what mentioned there. but you can use it the other way around too.

[@neneboy9173]

Great

[@mattmatt245]

Will I get the same result if I make all filters bi-directional ? Also, how would you handle this scenario in sql ?

[@RADACAD]

In this example, yes. However, I wouldn't recommend that.
SQL is totally different. there is no concept of DIRECTION of the relationship in the relational database. you can join tables on any way you want.

[@mattmatt245]

@@RADACAD So, if I join those tables in sql, according to your diagram, will it work ?

[@RADACAD]

If you join the tables in SQL Server, then the table you bring into Power BI will be one and the relationship will not be needed

[@mattmatt245]

@@RADACAD I don't want to use Power BI, just a query builder in SSMS. Will I get the same result ? Will many side filter one side ?

[@RADACAD]

@@mattmatt245 if you are using SSMS and query builder, then direction won't matter. join will cover it all

[@jayachandranjayapal5653]

Dire tional