Tips to sum values and ignore all filter but the fields of two table in Dax?

0
votes

I have 3 dimensions tables and one fact Table Sales

  • DimCalendar (Fields Year/Month/Day/Week)
  • DimCountry (Field : CountryName)
  • DimManager (Field ManagerName)
  • FctSales (Field : Amount)

I want to create a measure to Sum the Amount of the Sales (FctSales) and filter only to the fields of the tables DimCalendar and DimCountry.

After research, i was thinking about the function AllExcept, like :

CALCULATE(SUM(Sales[Amt]);ALLExcept(Sales;Country[Country];Calendar[Year]...)

but if i do that, i will have to write every columns of the table Calendar and Table Country in the AllExcept, i am wondering if there is another solution.

1
filterpowerbidaxssas-tabular
Is Offer your sales fact table? – Alexis Olson
Is your goal is to remove filters related to DimManager? – Alexis Olson
@AlexisOlson : The Sales is my fact table. It's not just to remove the filters related to DimManager, it's more : than i don't want any filter but the DimCountry/DimCalendar. (I will add more tables in the project and i want to filter only in the table DimCountry and DimCalendar only to this measure). – MoonLightFlower
You refer to Offer in your code but not FctSales. How are these related? – Alexis Olson
Sorry, it's a mistake, I just edit. – MoonLightFlower

1 Answers

1
votes

Maybe using REMOVEFILTERS() to remove every filter and then put back the filters over DimCountry and DimCalendar might work?

CALCULATE (
    SUM ( Sales[Amt] );
    REMOVEFILTERS ();
    VALUES( DimCountry[CountryName] );
    VALUES( DimCalendar[Date] )
)

DimCalendar[Date] should be the column used for the relationship with Sales. This measure first evaluates the filter arguments in the current filter context.

Using as filter the columns used for the relationships guarantees that whatever the column used for filtering this would be mapped over the relationship.

Then, the REMOVEFILTERS() will remove any existing context filter and eventually the filter arguments evaluated during the first step will be applied, putting back any filtering that was set over DimCalendar and DimCountry.