Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filter context in bi-directional model

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
Need Help