Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Measure not filtering with ALLEXCEPT function

Hello,

 

I have a clustered columnchart with 2 measures. 1 measure I would like to be affected by a slicer, the other not.

The one I would like not to be affected:

Msr FgTotaal = 
var AValue = 
    LOOKUPVALUE( TblGespresteerdeUren[SumHours],TblGespresteerdeUren[MonthYear], SELECTEDVALUE(DateTable[Year-Month]))
return CALCULATE(
    DIVIDE([MsrAO] *1000000, AValue),
    (Fcttable)
)

with:

MsrAO = 
CALCULATE(
            COUNTROWS(Fcttable),
            Fcttable[TypeOfIncident] = "xxxx"
        )

The filter Fcttable[TypeOfIncident] = "xxxx" must be maintained. 

So I tried the following:

 

Msr FgTotaal = 
var AValue = 
    LOOKUPVALUE( TblGespresteerdeUren[SumHours],TblGespresteerdeUren[MonthYear], SELECTEDVALUE(DateTable[Year-Month]))
return CALCULATE(
    DIVIDE([MsrAO] *1000000, AValue),
    ALLEXCEPT(Fcttable,Fcttable[TypeOfIncident])
)

By doing this, the measure is not affected by the slicer. but also not by the filter Fcttable[TypeOfIncident] = "xxxx".

In other words: using ALLEXCEPT or ALL yields the same result.

 

Does anyone see what I'm missing here?

 

Thx in advance!

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    I found that ALLEXCEPT did work.  But it was also affecting the X-axis. So I had to add a second filter exception from the datetable.

    Msr FgTotaal = 
var AValue = 
    LOOKUPVALUE( TblGespresteerdeUren[SumHours],TblGespresteerdeUren[MonthYear], SELECTEDVALUE(DateTable[Year-Month]))
return CALCULATE(
    DIVIDE([MsrAO] *1000000, AValue),
    ALLEXCEPT(Fcttable,Fcttable[TypeOfIncident], DateTable[Year-Month])
)

     grts

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Change this and check

     


    Msr FgTotaal =
    var AValue =
    LOOKUPVALUE( TblGespresteerdeUren[SumHours],TblGespresteerdeUren[MonthYear], SELECTEDVALUE(DateTable[Year-Month]))
    return CALCULATE(
    DIVIDE([MsrAO] *1000000, AValue),
    Filter(allselected(Fcttable),Fcttable[TypeOfIncident] = max(Fcttable[TypeOfIncident]))
    )

     

     

    allexcept will not allow any filter other than what is given in the allexcept (especially when used on fact)

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I found that ALLEXCEPT did work.  But it was also affecting the X-axis. So I had to add a second filter exception from the datetable.

      Msr FgTotaal = 
var AValue = 
    LOOKUPVALUE( TblGespresteerdeUren[SumHours],TblGespresteerdeUren[MonthYear], SELECTEDVALUE(DateTable[Year-Month]))
return CALCULATE(
    DIVIDE([MsrAO] *1000000, AValue),
    ALLEXCEPT(Fcttable,Fcttable[TypeOfIncident], DateTable[Year-Month])
)

       grts