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