Forum Discussion

nguyenk172's avatar
nguyenk172
New Member
4 years ago
Solved

Problem with Using SWITCH Function

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"
)
 
 
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    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

     

3 Replies

Replies have been turned off for this discussion
  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi nguyenk172 ,

     

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

     

    Thanks,

    Samarth

  • DarrenLau's avatar
    DarrenLau
    Advocate I
    4 years ago

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

     

  • v-luwang-msft's avatar
    v-luwang-msft
    Community Support
    4 years ago

    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