Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
Hi,
I am having issue with switch formula when creating custom columns from existing columns from same table;
Solved! Go to 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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYour 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.
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 :
Here is current output;
Based on SWITCH formula i should get below output;
Expected Output:
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:
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingMarch 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
217 | |
89 | |
76 | |
67 | |
60 |