filtering measures based on two columns in power bi dax

Question

I want to use a measure and filter the result based on the columns:

My measure is :

TotalProductionCon =
SUM ( _BI_SOVAC_PROD_KIT_LIFE_CYCLE[SGWCP8] )
    + SUM ( _BI_SOVAC_PROD_KIT_LIFE_CYCLE[retard] )

and I want it to summarize only when column année = column year.

I tried CALCULATE and FILTER;

TotalProductionCon =
CALCULATE (
    SUM ( _BI_SOVAC_PROD_KIT_LIFE_CYCLE[SGWCP8] )
        + SUM ( _BI_SOVAC_PROD_KIT_LIFE_CYCLE[retard] );
    FILTER (
        ALL ( _BI_SOVAC_PROD_KIT_LIFE_CYCLE[Année] );
        _BI_SOVAC_PROD_KIT_LIFE_CYCLE[Année] = _BI_SOVAC_PROD_KIT_LIFE_CYCLE[year]
    )
)

but it generates an error that the columns contain much value and I need to use aggregation.

Can you help me?

Have you tried filtering by the column only? i.e. TotalProductionCon = CALCULATE ( SUM ( _BI_SOVAC_PROD_KIT_LIFE_CYCLE[SGWCP8] ) + SUM ( _BI_SOVAC_PROD_KIT_LIFE_CYCLE[retard] ); _BI_SOVAC_PROD_KIT_LIFE_CYCLE[Année] = _BI_SOVAC_PROD_KIT_LIFE_CYCLE[year] ) This will reduce the memory required to process the DAX query. — StelioK
It generates this error :The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression. — M_M

RADO RADO · Accepted Answer · 2019-06-13T10:52:39

The problem with your formula is that you limited ALL function to only one column (Annee), and as a result FILTER does not "see" the other column it needs.

To fix that, change your formula as follows:

TotalProductionCon =
CALCULATE (
    SUM ( _BI_SOVAC_PROD_KIT_LIFE_CYCLE[SGWCP8] )
        + SUM ( _BI_SOVAC_PROD_KIT_LIFE_CYCLE[retard] );
    FILTER (
        ALL (
            _BI_SOVAC_PROD_KIT_LIFE_CYCLE[Année];
            _BI_SOVAC_PROD_KIT_LIFE_CYCLE[year]
        );
        _BI_SOVAC_PROD_KIT_LIFE_CYCLE[Année] = _BI_SOVAC_PROD_KIT_LIFE_CYCLE[year]
    )
)

I am assuming here that your choice of ALL function is appropriate; otherwise you might need to use a different technique such as SUMMARIZE function.

filtering measures based on two columns in power bi dax

1 Answers