Problem with Using SWITCH Function

[nguyenk172]

I am trying to use switch to solve multiple IF statement. But this DAX formula gives me errror as shown below: 

Function 'SWITCH' does not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

I wonder if anyone knows how to fix this. Much appreciated

This my DAX formula

Type =

SWITCH(

True(),

ISBLANK(PurchaseOrders[Agreement_KONNR]) && PurchaseOrders[CreatedBy_ERNAM] ="BATCHJOB","UC4BATCH" && PurchaseOrders[DocumentType_BSART]="ZB","Z1","SPA",

Not ISBLANK(PurchaseOrders[Agreement_KONNR]) && PurchaseOrders[CreatedBy_ERNAM] ="BATCHJOB","UC4BATCH","Auto Calloff",

Not ISBLANK(PurchaseOrders[Agreement_KONNR]) && Not PurchaseOrders[CreatedBy_ERNAM]="BATCHJOB","UC4BATCH","Buyer Intervention",

"Spot PO"
)

[v-luwang-msft · Accepted solution]

Hi @nguyenk172 ,

The formula you are using needs to be split and cannot be equal to one data and then directly equal to another value.

Refer the following sample ,as you provided:

Type1 = 
SWITCH (
    TRUE (),
    ISBLANK ( PurchaseOrders[Agreement_KONNR] )
        && PurchaseOrders[CreatedBy_ERNAM] = "BATCHJOB",
        "UC4BATCH"
            && PurchaseOrders[DocumentType_BSART] = "ZB",
    "Z1", "SPA",
  
    "Spot PO"
)

Get:

And after adjust:

Type =
SWITCH (
    TRUE (),
    ISBLANK ( PurchaseOrders[Agreement_KONNR] )
        && ( PurchaseOrders[CreatedBy_ERNAM] = "BATCHJOB"
        || PurchaseOrders[CreatedBy_ERNAM] = "UC4BATCH" )
        && ( PurchaseOrders[DocumentType_BSART] = "ZB"
        || PurchaseOrders[DocumentType_BSART] = "Z1" ), "SPA",
    "Spot PO"
)

Output:

And according your provided data,you need to adjust to the below:

Type1 = 
SWITCH (
    TRUE (),
    ISBLANK ( PurchaseOrders[Agreement_KONNR] )
        && (PurchaseOrders[CreatedBy_ERNAM] = "BATCHJOB"||PurchaseOrders[CreatedBy_ERNAM] = "UC4BATCH"
        )
            && (PurchaseOrders[DocumentType_BSART] = "ZB"||PurchaseOrders[DocumentType_BSART]= "Z1")
    , "SPA",
    NOT ISBLANK ( PurchaseOrders[Agreement_KONNR] )
        && (PurchaseOrders[CreatedBy_ERNAM] = "BATCHJOB"||PurchaseOrders[CreatedBy_ERNAM] = "UC4BATCH"),
    "Auto Calloff",
        NOT ISBLANK ( PurchaseOrders[Agreement_KONNR] )
            && (NOT PurchaseOrders[CreatedBy_ERNAM] = "BATCHJOB"||NOT PurchaseOrders[CreatedBy_ERNAM] = "UC4BATCH"),
    "Buyer Intervention",
    "Spot PO"
)

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[DarrenLau]

Hi,

Found your question interesting and I came up with the following. Hope it helps and at the very least, points you to the right direction to get your formula working.

Assign Status = 
VAR _checkAgreement = IF ( MAX ( Purchases[AgreementID] ) = "-", TRUE(),FALSE())
VAR _checkCreated = OR ( MAX (Purchases[CreatedBy])="BATCHJOB", MAX(Purchases[CreatedBy])="UC4BATCH")
VAR _checkDocType = OR ( MAX (Purchases[DocumentType])="Z1", MAX(Purchases[DocumentType])="ZB")
VAR _status = 
    SWITCH(TRUE(),
        _checkAgreement && _checkCreated && _checkDocType, "SPA",
        _checkAgreement = FALSE() && _checkCreated, "Auto Calloff",
        _checkAgreement = FALSE() && _checkCreated = FALSE(),"Buyer Intervention",
        "Spot PO"  
    )
RETURN
    _status

Link to example PBIX for reference - SWITCH example 

I had some trouble with the ISBLANK() function so I substituted "-" into the data as a check to save time.

Example table and results to check logic:-

[Samarth_18]

Hi @nguyenk172 ,

Looks some issue in switch conditions, Could you please share the sample data in text format with expected output?

Thanks,

Samarth