Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
damit23183
Microsoft Employee
Microsoft Employee

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,
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
)
 
Based on the above formula, i am only able to get 1,2,5,6 in output but i saw full table and found that
there are records available in table which satisfy 3 & 4 condition. Thereofre, i should see all values form 1 to 6 in
this custom column which i am creating.
 
NOTE: All of these conditonal columns are from same table. Here i am trying to create new custom columns.
 
I have one record which satisfy condtion where output shoule be 3 but i am getting 2. It does look like syntex
is only looking at first condition which is NOT(ISBLANK([Date Agreed])). Therefore, i am getting 2 instead of 3.
 
Any help will be appreciated!
 
Thanks
1 ACCEPTED SOLUTION

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.

 

2020-03-08 06_00_03-Untitled - Power BI Desktop.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

3 REPLIES 3
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

 

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 :

 

Source Table.JPG

 

Here is current output;

 

Current Output.JPG

 

Based on SWITCH formula i should get below output;

Expected Output:

 

Expected Output.JPG

                                                                                                                                                                   

 

 

 

Hope this will help you.

 

Thanks           

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.

 

2020-03-08 06_00_03-Untitled - Power BI Desktop.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.