KEEPFILTERS works fine with measure but unexpected within CALCULATE/FILTER combination

[Anonymous]

Hi community,

please help me with joining my two DAX measures into one since my current solution almost doubles count of my measures.

I have a fact table (general ledger) on which I'm calculating sums for different periods and compare them. There is also a slicer plan_ID for planned data, which are in the same fact table (regular and planned data are splitted through field ID_VKG). For regular sums I have to remove PLAN filter. So I've managed to find the right result in two steps:

Value_D = 
var date_from = MIN(calendar[date])
var date_to = MAX(calendar[date])
return
  CALCULATE(SUM(fact_table[DEBET]), FILTER(fact_table, fact_table[date] >= date_from && fact_table[date] <= date_to && fact_table[ID_VKG] = 1))

Value_DD = CALCULATE( [Value D], REMOVEFILTERS( fact_table[PLAN] ) )

When I tried to include REMOVEFILTERS in measure Value_D to avoid two measures, no records were returned.

Value_D = 
var date_from = MIN(calendar[date])
var date_to = MAX(calendar[date])
return
  CALCULATE(SUM(fact_table[DEBET]), REMOVEFILTERS( fact_table[PLAN] ), FILTER(fact_table, fact_table[date] >= date_from && fact_table[date] <= date_to && fact_table[ID_VKG] = 1))

Please can someone help me with that?

Regards,

[johnt75 · Accepted solution]

Try

Value_D =
VAR date_from =
    MIN ( calendar[date] )
VAR date_to =
    MAX ( calendar[date] )
RETURN
    CALCULATE (
        SUM ( fact_table[DEBET] ),
        REMOVEFILTERS ( fact_table[PLAN] ),
        fact_table[date] >= date_from
            && fact_table[date] <= date_to
            && fact_table[ID_VKG] = 1
    )

Part of the problem is that you are using the FILTER function as a filter in CALCULATE, which is not best practice. By using a full table as a filter, rather than just the columns you need, the entire expanded table gets put into the filter context.

Also, make sure that the column you pass into the REMOVEFILTERS is the actual column which is being filtered. If the column comes from a dimension table not the fact table then you need to use the dimension table in REMOVEFILTERS

[Anonymous]

Excellent. Thank you very much @johnt75. It perfectly works and I've learned a lot from your post. Appreciate your help. Thanks!