topic Re: Add columns to ALLEXCEPT formula only if the columns are filtered in DAX Commands and Tips

Add columns to ALLEXCEPT formula only if the columns are filtered

Natalia10000 — Wed, 28 Aug 2024 15:58:19 GMT

Hello,

I have the following measure

PercentageofTotal2 =

var TotalKPI = CALCULATE(

[Calculation KPI],

ALLEXCEPT(dimarticle,dimarticle[MerchandiseCategoryACGDesc],dimarticle[ProductAllocation]))

RETURN

DIVIDE([Calculation KPI],TotalKPI)

I would like to add additional columns from table dimarticle to ALLEXCEPT but only if the user will filter them, so e.g. there is slicer dimarticle[Category], so if the user filters sth in this slicer, the formula should look like that:

PercentageofTotal2 =

var TotalKPI = CALCULATE(

[Calculation KPI],

ALLEXCEPT(dimarticle,dimarticle[MerchandiseCategoryACGDesc],dimarticle[ProductAllocation],dimarticle[Category]))

RETURN

DIVIDE([Calculation KPI],TotalKPI)

Could You please help me with that?

Kind reagrds,

Natalia

Re: Add columns to ALLEXCEPT formula only if the columns are filtered

Anonymous — Thu, 29 Aug 2024 01:37:24 GMT

Hi Natalia10000 ,

You can use ISFILTERED function to check if a column is being filtered.

PercentageofTotal2 = VAR TotalKPI1 = CALCULATE ( [Calculation KPI], ALLEXCEPT ( dimarticle, dimarticle[MerchandiseCategoryACGDesc], dimarticle[ProductAllocation] ) ) VAR _1 = DIVIDE ( [Calculation KPI], TotalKPI1 ) VAR TotalKPI2 = CALCULATE ( [Calculation KPI], ALLEXCEPT ( dimarticle, dimarticle[MerchandiseCategoryACGDesc], dimarticle[ProductAllocation], dimarticle[Category] ) ) VAR _2 = DIVIDE ( [Calculation KPI], TotalKPI2 ) RETURN IF ( ISFILTERED ( dimarticle[Category] ), _2, _1 )

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

Re: Add columns to ALLEXCEPT formula only if the columns are filtered

Natalia10000 — Thu, 29 Aug 2024 06:38:01 GMT

Thank You, but do You know another way? Because I would need to add more to ALLEXCEPT because I have several filters on dimarticle, if I would use ISFILETERED that would mean lots of conditions

Re: Add columns to ALLEXCEPT formula only if the columns are filtered

Anonymous — Thu, 29 Aug 2024 07:59:36 GMT

Hi Natalia10000 ,

I understand what you mean, but there is no way to dynamically add parameters to ALLEXCEPT, the method in reply 2 is an alternative. Or you could consider the ALLSELECTED function and see if it achieves your desired result.

PercentageofTotal2 = var TotalKPI = CALCULATE( [Calculation KPI], ALLSELECTED(dimarticle)) RETURN DIVIDE([Calculation KPI],TotalKPI)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.