Forum Discussion

damit23183's avatar
damit23183
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Result from SWITHC not as expected

Hi,   I am having issue with switch formula when creating custom columns from existing columns from same table;   SWITCH(TRUE(), NOT(ISBLANK([Date Agreed])) && NOT(ISBLANK([Contact Date])), 1, ...
Need Help
  • edhans's avatar
    edhans
    6 years ago

    Thanks for the data. Your data doesn't allow for the results you want. For example, in your data, row 2 and 4 are identical:

    • Date Agreed is null
    • Contact Date is not null
    • Status Name is "Accepted

    There is no other critera for the SWITCH to return a 2 for row 2 and a 4 for row 4.

    As for the others that were off, the below will fix it. 

    Result = 
SWITCH (
    TRUE (),
    NOT ( ISBLANK ( [Date Agreed] ) ) && NOT ( ISBLANK ( [Contact Date] ) ), 1,
    ISBLANK ( [Date Agreed] ) && NOT ( ISBLANK ( [Contact Date] ) ) && [Status Name] = "Accepted", 2,
    NOT ( ISBLANK ( [Date Agreed] ) )
        && [Status Name] = "Accepted", 3,
    ISBLANK ( [Date Agreed] )
        && [Status Name] = "Accepted", 4,
    NOT ( ISBLANK ( [Date Agreed] ) ) && [Status Name] = "Rejected", 5,
    ISBLANK ( [Date Agreed] ) && [Status Name] = "Rejected", 6
)

    You can see my results below, and the red boxes highlighted what I changed in your Calculated Column formula.

     

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

Your formula looks ok:

Data =
SWITCH (
    TRUE (),
    NOT ( ISBLANK ( [Date Agreed] ) ) && NOT ( ISBLANK ( [Contact Date] ) ), 1,
    ISBLANK ( [Date Agreed] ) && NOT ( ISBLANK ( [Contact Date] ) ), 2,
    NOT ( ISBLANK ( [Date Agreed] ) )
        && [Status Name] = "Accepted", 3,
    ISBLANK ( [Date Agreed] )
        && [Status Name] = "Accepted", 4,
    NOT ( ISBLANK ( [Date Agreed] ) ), 5,
    ISBLANK ( [Date Agreed] ), 6
)

I cannot really troubleshoot without the data. For example, if there are any leading or trailing spaces in [Status Name] then it won't work, but without the data we cannot see that. "Accepted" <> "Accepted " for example. DAX is not case sensitive though like Power Query is, so that isn't it.

  • damit23183's avatar
    damit23183
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

     

    Hi,

    Thank you for your response.

    Here are details from data..sorry can't share real data due to privacy but have created exactly same table;

     

    This is Source Table :

     

     

    Here is current output;

     

     

    Based on SWITCH formula i should get below output;

    Expected Output:

     

                                                                                                                                                                       

     

     

     

    Hope this will help you.

     

    Thanks           

    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Thanks for the data. Your data doesn't allow for the results you want. For example, in your data, row 2 and 4 are identical:

      • Date Agreed is null
      • Contact Date is not null
      • Status Name is "Accepted

      There is no other critera for the SWITCH to return a 2 for row 2 and a 4 for row 4.

      As for the others that were off, the below will fix it. 

      Result = 
SWITCH (
    TRUE (),
    NOT ( ISBLANK ( [Date Agreed] ) ) && NOT ( ISBLANK ( [Contact Date] ) ), 1,
    ISBLANK ( [Date Agreed] ) && NOT ( ISBLANK ( [Contact Date] ) ) && [Status Name] = "Accepted", 2,
    NOT ( ISBLANK ( [Date Agreed] ) )
        && [Status Name] = "Accepted", 3,
    ISBLANK ( [Date Agreed] )
        && [Status Name] = "Accepted", 4,
    NOT ( ISBLANK ( [Date Agreed] ) ) && [Status Name] = "Rejected", 5,
    ISBLANK ( [Date Agreed] ) && [Status Name] = "Rejected", 6
)

      You can see my results below, and the red boxes highlighted what I changed in your Calculated Column formula.