Forum Discussion

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 : [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

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.

sample (1) (6).xlsx19.3 MB

littlevillage

Iron Contributor

Jan 02, 2023

SergeiBaklan

Tỉ lệ PU đúng giờ:=[Đơn PU đúng giờ]/[Tổng đơn nhận]

Đơn PU đú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 "Tỉ lệ PU đúng giơ" with logic like above,̀

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

//[Tỉ lệ PU đúng giờ] < "99%",

Hope for your response

Thank you.

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

sample (1) (7).xlsx20 MB

littlevillage

Iron Contributor

Jan 08, 2023

SergeiBaklan

Thank you for response,

The logic like:

=SUMIFS(Data[Copy of Sample],Data[Mã khách hàng],(E10:E15),Data[Trạng thái đơn hàng],(F10:F15),Data[Tổng phải lấy],(G10:G15),Data[LATE],(H10:H15),Data[Tỉ lệ PU đúng giờ],"<99%")

How can we do the same that in Power Pivot,

SergeiBaklan

Diamond Contributor

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