Nested IF statements and filtering on a column

[KHP]

I have a measure that calculates the vendor rank based on revenue.  I am attempting to return a 1 if the rank is >=10 if it includes "NTTD" or return 1 for the rank <10 and 1 for "NTTD."  In the or statement i am trying to ensure that NTTD is marked as 1.  This throws an error "a single column for "vendor" in "apptbl_vendor" cannot be determined.  I have asked chatgpt and it is no help with this.  any input is greatly appreciated.

IsTopVendor =

IF(

    CALCULATE([Vendor Rank],app_tblVendors[Vendor]="NTTD")<=10,

    IF(

        CALCULATE([Vendor Rank])<=10,

        1,

        0

    ),

    If(

        CALCULATE([Vendor Rank],app_tblVendors[Vendor]="NTTD")>10,

        If(

            OR(

                app_tblVendors[Vendor]="NTTD",

                CALCULATE([Vendor Rank])<10

            )

        1,

        0

        )

    )

)

[AlexisOlson · Accepted solution]

The error is in this line:

app_tblVendors[Vendor]="NTTD",

You are comparing a column to a string without specifying a specific row in that column.

Try this instead since you wanted to check if it includes "NTTD":

"NTTD" IN VALUES ( app_tblVendors[Vendor] )

As a side note, I much prefer using SWITCH to nested IF statements and using variables helps too.

Try this refactoring:

IsTopVendor =
VAR _Rank = [Vendor Rank]
VAR _RankNTTD = CALCULATE ( [Vendor Rank], app_tblVendors[Vendor] = "NTTD" )
VAR _IncludesNTTD = "NTTD" IN VALUES ( app_tblVendors[Vendor] )
RETURN
    SWITCH (
        TRUE (),
        _RankNTTD <= 10 && _Rank <= 10, 1,
        _RankNTTD >  10 && ( _Rank < 10 || _IncludesNTTD ), 1,
        0
    )