CODEWITHSUNDEEP
filterpowerbidaxssas-tabular
MoonLightFlower
MoonLightFlower

Reputation: 183

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

I have 3 dimensions tables and one fact Table Sales

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.

Upvotes: 1

Views: 11066

Answers (1)

sergiom
sergiom

Reputation: 4877

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.

Upvotes: 3

Related Questions