SOLVED

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

Iron Contributor

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]),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")))

 

small_village_0-1671648742835.png

But it not working.

Some better ideas, please.

Hope for your help.

Thank you.

 

12 Replies

@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"
        )
    )
)

 

@Sergei Baklan 

Thank you for response

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

small_village_1-1672154295590.png

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

Hope for your help.

 

 

 

 

@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

image.png

Filter returns 85 records:

image.png

Same result. The only, measure works much faster.

 

@Sergei Baklan 

I am sorry for my idea not clear

the formula is missing a condition :

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

small_village_0-1672355394022.png

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

small_village_7-1672357101120.png

 

 

@Sergei Baklan 

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.

 

@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. 

 

@Sergei Baklan 

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[Tỉ lệ PU đúng giờ],"<99%")

small_village_0-1673152283320.png

How can we do the same that in Power Pivot,

 

best response confirmed by littlevillage (Iron Contributor)
Solution

@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

image.png

@Sergei Baklan 

Thank you very much,

Expected results are 100% correctly.
it's just that i still don't quite understand, if i add 2 fields "Mã vận đơn" and "Trạng thái đơn hàng" as shown then result still 85 instead of 82 because 239D01 and 237H01 have been added respectively.

small_village_1-1674623406012.png

 

@littlevillage 

in Final count measure we summarize based on Machinhanh table

...SUMX ( Machinhanh, [Intermediate count] )

it doesn't matter will you add fields from Data table into PivotTable or not. Thus result is the same, 82.

 

Do you mean it shall be 85 if any field from Data table is added?

@Sergei Baklan 

in the image below, I have added 2 fields (Mã vận đơn and Trạng thái đơn hàng), it still show 82 but actually, sum of Final count column is 85.  May be 239D01 and 237H01 should be removed becasue "Tỉ lệ PU đúng giờ" > 99% then result is 82.

Hope for your response.

small_village_1-1674660518322.png

 

 

@littlevillage 

It depends on what you'd like to receive. Value in Grand Total has no connection with values in the column. Grand Total is calculated by separate formula, thus not necessary equal to the sum of column values.

1 best response

Accepted Solutions
best response confirmed by littlevillage (Iron Contributor)
Solution

@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

image.png

View solution in original post