Add columns to ALLEXCEPT formula only if the columns are filtered

[Natalia10000]

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

[Anonymous]

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. 

[Natalia10000]

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

[Anonymous · Accepted solution]

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.