Filter context in bi-directional model

[Anonymous]

Hi,

I'm facing a problem on filter context in bi-directional model. I need a bi-direction relationship because i need a count in dimension can work in other dims.

For example, from AdvantureWorkDW, i load FactInternetSales, DimDate, DimCustomer.  And create relationship between Fact & DimCustomer as bi-direction, single direction between fact & DimDate.

Then, i create a meausre CustomerCount:=

DISTINCTCOUNT(DimCustomer[CustomerKey]). it's working as expected when I pull CalendarYear & CustomerCount into a table, it shows different no of customer per year.

Now, i would like to calculate below, 

Total Customer by Geo = CALCULATE(DISTINCTCOUNT(DimCustomer[CustomerKey]), ALLEXCEPT(DimCustomer, DimCustomer[GeographyKey])), this measure needs to ignore filter context from other dim/fact so that it's real Total. Unfortunately, it's not working as expected when drag 

GeographyKey into pivot. 

So i tried below to overwrite filter context, but it will give grand total and ignore GeographyKey filter.

Total Customer by Geo 2 = CALCULATE(DISTINCTCOUNT(DimCustomer[CustomerKey]), ALLEXCEPT(DimCustomer, DimCustomer[GeographyKey]), ALL(FactInternetSales))

Any advise?

Thanks, Di

[LivioLanzo · Accepted solution]

Hi @Anonymous

I would discourage the use of bi-directional relationships:

https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/

You can use cross filter to simulate the biredictional filter temporarily

https://www.wiseowl.co.uk/blog/s2597/cross_filter.htm

[Anonymous]

Awsome, thank you very much for such prompt response.