Forum Discussion

littlevillage's avatar
littlevillage
Iron Contributor
Dec 21, 2022
Solved

CALCULATE function not allowed use in a TRUE/FALSE expression.

Hi, I would like to filter data with criteria : [Tỉ lệ PU đúng giờ]<"99%" I try to write a sample function as below in Power Pivot.   Sample function:=CALCULATE(COUNTA(Data[Bưu cục gửi hàng]...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 10, 2023

    littlevillage 

    I combined initial measures into one, but you may return back

    Intermediate count:=VAR filter1 =
    CALCULATETABLE (
        Data,
        Data[Tổng phải lấy] = "Lấy",
        OR (
            OR (
                OR ( Data[Mã khách hàng] = "084LC00010", Data[Mã khách hàng] = "084LC00016" ),
                OR ( Data[Mã khách hàng] = "084LC00005", Data[Mã khách hàng] = "084LC00053" )
            ),
            OR ( Data[Mã khách hàng] = "084LC00205", Data[Mã khách hàng] = "084LC00076" )
        )
    )
VAR filter2 =
    CALCULATETABLE ( Data, FILTER ( filter1, Data[PU đúng giờ] = "PU đúng giờ" ) )
VAR filter3 =
    CALCULATETABLE ( Data, FILTER ( filter1, Data[LATE] = "Trễ" ) )
VAR filter4 =
    CALCULATETABLE (
        Data,
        FILTER (
            filter3,
            OR (
                OR (
                    Data[Trạng thái đơn hàng] = "abnormal parcel",
                    Data[Trạng thái đơn hàng] = "DISPATCH"
                ),
                OR (
                    Data[Trạng thái đơn hàng] = "PICKUP FAILED",
                    Data[Trạng thái đơn hàng] = "PUSAT_DISPATCH"
                )
            )
        )
    )
VAR counting =
    IF (
        COUNTROWS ( filter2 ) / COUNTROWS ( filter1 ) < 0.99,
        COUNTROWS ( filter4 ),
        BLANK ()
    )
RETURN
    counting

    Based on it final measure

    Final count:=IF (
    HASONEVALUE ( Data[Bưu cục gửi hàng] ),
    [Intermediate count],
    SUMX ( Machinhanh, [Intermediate count] )
)

    Result is

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 21, 2022

littlevillage 

Filter in CALCULATE() iterates table and we shall work with columns (fields) here, not with measures.

Workaround depends on desired calculation logic and what is behind this measure.

Sample:=CALCULATE (
    COUNTA ( Data[Bưu cục gửi hàng] ),
    Data[Tổng phải lấy] = "Lấy",
    //[Tỉ lệ PU đúng giờ] < "99%",
    Data[LATE] = "Trễ",
    OR (
        OR (
            OR ( Data[Mã khách hàng] = "084LC00010", Data[Mã khách hàng] = "084LC00016" ),
            OR ( Data[Mã khách hàng] = "084LC00005", Data[Mã khách hàng] = "084LC00053" )
        ),
        OR ( Data[Mã khách hàng] = "084LC00205", Data[Mã khách hàng] = "084LC00076" )
    ),
    OR (
        OR (
            Data[Trạng thái đơn hàng] = "abnormal parcel",
            Data[Trạng thái đơn hàng] = "DISPATCH"
        ),
        OR (
            Data[Trạng thái đơn hàng] = "PICKUP FAILED",
            Data[Trạng thái đơn hàng] = "PUSAT_DISPATCH"
        )
    )
)

 

littlevillage's avatar
littlevillage
Iron Contributor
Dec 27, 2022

SergeiBaklan 

Thank you for response

In my original data, i used your code in Power Pivot to create a pivot table:

the result shows 85 units, but the expected result is 82 units.

Hope for your help.

 

 

 

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Dec 28, 2022

    littlevillage 

    Sorry, but I don't know what shall correct logic. However, we may check existing logic directly. Within measure we have 4 conditions to calculate

    Sample:=CALCULATE (
    COUNTA ( Data[Bưu cục gửi hàng] ),
    
    // CONDITION 1
    Data[Tổng phải lấy] = "Lấy",
    //[Tỉ lệ PU đúng giờ] < "99%",
    
    // CONDITION 2
    Data[LATE] = "Trễ",
    
    // CONDITION 3
    OR (
        OR (
            OR ( Data[Mã khách hàng] = "084LC00010", Data[Mã khách hàng] = "084LC00016" ),
            OR ( Data[Mã khách hàng] = "084LC00005", Data[Mã khách hàng] = "084LC00053" )
        ),
        OR ( Data[Mã khách hàng] = "084LC00205", Data[Mã khách hàng] = "084LC00076" )
    ),
    
    // CONDITION 4
    OR (
        OR (
            Data[Trạng thái đơn hàng] = "abnormal parcel",
            Data[Trạng thái đơn hàng] = "DISPATCH"
        ),
        OR (
            Data[Trạng thái đơn hàng] = "PICKUP FAILED",
            Data[Trạng thái đơn hàng] = "PUSAT_DISPATCH"
        )
    )
)

    which are combined with AND. We may create calculated columns for each of conditions and another column which ANDs them, after that filter it on TRUE

    Filter returns 85 records:

    Same result. The only, measure works much faster.

     

    • littlevillage's avatar
      littlevillage
      Iron Contributor
      Jan 02, 2023

      SergeiBaklan 

      Tỉ lệ PU đúng giờ:=[Đơn PU đúng giờ]/[Tổng đơn nhận]

Đơn PU đúng giờ:=CALCULATE(COUNTA(Data[Bưu cục gửi hàng]),Data[PU đúng giờ]="PU đúng giờ",Data[Tổng phải lấy]="Lấy",OR(OR(OR(Data[Mã khách hàng]="084LC00010",Data[Mã khách hàng]="084LC00016"),OR(Data[Mã khách hàng]="084LC00005",Data[Mã khách hàng]="084LC00053")),OR(Data[Mã khách hàng]="084LC00205",Data[Mã khách hàng]="084LC00076")))

Tổng đơn nhận:=CALCULATE(COUNTA(Data[Bưu cục gửi hàng]),Data[Tổng phải lấy]="Lấy",OR(OR(OR(Data[Mã khách hàng]="084LC00010",Data[Mã khách hàng]="084LC00016"),OR(Data[Mã khách hàng]="084LC00005",Data[Mã khách hàng]="084LC00053")),OR(Data[Mã khách hàng]="084LC00205",Data[Mã khách hàng]="084LC00076")))

      I try to create a column of "Tỉ lệ PU đúng giơ" with logic like above,̀

      and then reference that column to the function as a criteria: 

      //[Tỉ lệ PU đúng giờ] < "99%",

      Hope for your response

      Thank you.

       

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 03, 2023

        littlevillage 

        It's not necessary to add calculated column, you may use measure as

        Copy of Sample:=
VAR filtered =
    ADDCOLUMNS ( Data, "ourMeasure", [Tỉ lệ PU đúng giờ] )
RETURN
    CALCULATE (
        COUNTA ( Data[Bưu cục gửi hàng] ),
        // CONDITION 1
        Data[Tổng phải lấy] = "Lấy",
        //[Tỉ lệ̣ PUđúng giờ] < "99%",
        // CONDITION 2
        Data[LATE] = "Trễ",
        // CONDITION 3
        OR (
            OR (
                OR ( Data[Mã khách hàng] = "084LC00010", Data[Mã khách hàng] = "084LC00016" ),
                OR ( Data[Mã khách hàng] = "084LC00005", Data[Mã khách hàng] = "084LC00053" )
            ),
            OR ( Data[Mã khách hàng] = "084LC00205", Data[Mã khách hàng] = "084LC00076" )
        ),
        // CONDITION 4
        OR (
            OR (
                Data[Trạng thái đơn hàng] = "abnormal parcel",
                Data[Trạng thái đơn hàng] = "DISPATCH"
            ),
            OR (
                Data[Trạng thái đơn hàng] = "PICKUP FAILED",
                Data[Trạng thái đơn hàng] = "PUSAT_DISPATCH"
            )
        ),
        FILTER ( filtered, [ourMeasure] < 0.99 )
    )

        but result is exactly the same, 85. Perhaps you may explain why the records you highlighted in your sample shall be excluded, the logic will be more clear. 

         

    • littlevillage's avatar
      littlevillage
      Iron Contributor
      Dec 29, 2022

      SergeiBaklan 

      I am sorry for my idea not clear

      the formula is missing a condition :

      [Tỉ lệ PU đúng giơ] < "99%"̀

      If it's worked,

      239D01 with [Tỉ lệ PU đúng giơ] =99.79%, so  Sample column not count 1 unit for 239D01

      237H01 with [Tỉ lệ PU đúng giơ] =99.28%, so  Sample column not count 2 unit for 237H01

      the expected result is 82 instead of 85

       

       

Resources