Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

ALL function not working when multiple page filters are used.

I have 2 tables Table1 and Table2 (Both are linked using a "PKID")and I have a PowerBI page where I use page filters from
Table1 --- Table1[FID] and Table1[OID] and I have a table widget in the page displaying 2 columns and the below measures from Table2 (I'm trying to get numbers and percentage values)

FNumbers = CALCULATE(sum(Table2[validRecordbit]))

FPercentage = VAR YesCID =CALCULATE(sum(Table2[validRecordbit]))
              VAR AllCID = CALCULATE(sum(Table2[validRecordbit])
                           ,FILTER(ALLSELECTED(Table2),Table2[Acol] = MAX(Table2[Acol])))
              VAR RESULT = DIVIDE(YesCID, AllCID, 0)
              RETURN
              RESULT

ONumbers = CALCULATE(sum(Table2[validRecordbit]),ALL(Table1[FID]))
Opercentage = VAR YesCID =CALCULATE(sum(Table2[validRecordbit])
                                    ,ALL(Table1[FID]))
              VAR AllCID = CALCULATE(sum(Table2[validRecordbit])
                           ,FILTER(ALLSELECTED(Table2),Table2[Acol] = MAX(Table2[Acol]))
                           ,ALL(Table1[FID]))
             VAR RESULT = DIVIDE(YesCID, AllCID, 0)
             RETURN
             RESULT


All the above measures are working fine when just page filter (Table1[OrgID]) is used.

But when using both the page filters (Table1[FID] and Table1[OID]) the values returning for the measure "Opercentage" is incorrect and the below code is ignoring the ALL(Table1[FID]) and using the Fnumber values for division

VAR AllCID = CALCULATE(sum(Table2[validRecordbit])
                           ,FILTER(ALLSELECTED(Table2),Table2[Acol] = MAX(Table2[Acol]))
                           ,ALL(Table1[FID]))

. Why ALL(Table1[FID]) not working for Opercentage when both page filters are used-- {I'm just trying to get sum of validrecordbit column groupby(Acol)}

  • Anonymous's avatar
    Anonymous
    5 years ago

    Using the below code 

    VAR AllCID  = CALCULATE(sum(Table2[validRecordbit])
    ,ALLEXCEPT(Table2,Table2[Acol],Table1[OID])
    )

    got it worked.  Replace FID with OID since ALLEXCEPT will remove all other filters except the ones mentioned.  

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Using the below code 

    VAR AllCID  = CALCULATE(sum(Table2[validRecordbit])
    ,ALLEXCEPT(Table2,Table2[Acol],Table1[OID])
    )

    got it worked.  Replace FID with OID since ALLEXCEPT will remove all other filters except the ones mentioned.