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

Hi, I would like to filter data with criteria : [Tỉ lệ PU đú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]...

SampleCaculated 21.12.xlsx19.1 MB

excel

Formulas and Functions

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

sample (1) (8).xlsx20 MB

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

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",
    //[Tỉ lệ PU đú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.

Forum Discussion

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

Resources