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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
maheshbabu
Regular Visitor

Multiple conditions in Switch Function based on two calculated columns in PowerBI

maheshbabu_0-1684058001378.png

Hi guys, any help to the above switch column is highly appreciable. I am dealing with two calculated columns which you can see on the right side of image, one is Digital and another one is NonDigital. Digital column is created using the following dax: 

Digital = CALCULATE(MIN(Communications[Daysdiff]),ALLEXCEPT(Communications,Communications[DebtorNumber]),Communications[Digital/Non-Digital]="Digital")
and NonDigital is created using : 
NonDigital = CALCULATE(MIN(Communications[Daysdiff]),ALLEXCEPT(Communications,Communications[DebtorNumber]),Communications[Digital/Non-Digital]="Non-Digital")
Both the columns returns number of days between firstcommdate(please refer image) and firstpaydate(please refer image).
Generally we sent multiple communications to each debtor(a column which contains duplicates, beacuse each debtor receives multiple communications) by digitally and non digitally and few debtors only receives communications either only by digital or non digital mode. 
firstcommdate = most earliest date of communication sent to each debtor by digital and nondigital. It contains two earliest dates(one for digital and one for non digital) for each debtor if he receives both digital and non digital else only one date.
firstpaydate = it contains only one earliest date for each debtor irrespective of types of communications he received.
Based on the above two columns I have created Digital and NonDigital columns to find the minimum of days difference for each debtor per communication type. 
Now my requirement is to categorize each debtor as either Digital or NonDigital based on the difference between firstpaydate and earliest firstcommdate(why earliest firstcommdate is - if a debtor receives both digital and non digital communication, he/she will have two firstcommdates one for digital and one for non digital, among those two dates, need to consider earliest date).
I have written the switch statement which you can see in the image, all the conditions are satisfying but not the last. So kindly help me to achieve the desired output either by correcting above DAX or providing new DAX. The conditions are as below:
1.Both the digital and nondigital columns contain negatives, positives, zero and blanks.
2.Both digital and nondigital greater than zero and if digital less than nondigital,"Digital"
3.Both digital and nondigital greater than zero and if nondigital less than digital,"Non-Digital"
4.digital equals to non digital, blank,
5.digital equals/greater than to zero and nondigital less than zero,"Digital",
6.nondigital equals/greater than to zero and digital less than zero,"Non-Digital",
7. digital equals to zero and nondigital greater than or less than zero "Digital",
8.nondigital equals to zero and digital greater than or less than zero "Non-Digital",
9.digital is blank and nondigital is zero or greater than zero,"Non-Digital"
10.nondigital is blank and digital is zero or greater than zero,"Digital", blank.

Thanks in Advance.



3 REPLIES 3
amitchandak
Super User
Super User

@maheshbabu ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

 

try if this can help

New column =
SWITCH (
TRUE (),
ISBLANK ( Communications[NonDigital] ), "Digital",
ISBLANK ( Communications[Digital] ), "Non-Digital",
Communications[Digital] < Communications[NonDigital], "Digital",
Communications[Digital] > Communications[NonDigital], "Non-Digital",
Communications[Digital] = Communications[NonDigital], BLANK (),
Communications[Digital] >= 0 && Communications[NonDigital] < 0, "Digital",
Communications[Digital] < 0 && Communications[NonDigital] >= 0, "Non-Digital",
Communications[Digital] = 0 && Communications[NonDigital] <> 0, "Digital",
Communications[Digital] <> 0 && Communications[NonDigital] = 0, "Non-Digital",
Communications[Digital] = 0 && Communications[NonDigital] >= 0, "Digital",
Communications[NonDigital] = 0 && Communications[Digital] >= 0, "Non-Digital",
Communications[Digital] = BLANK () && Communications[NonDigital] >= 0, "Non-Digital",
Communications[NonDigital] = BLANK () && Communications[Digital] >= 0, "Digital",
BLANK ()
)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi Amit,
I have tried the DAX that you provided, but in vain. Here I am pasting the desired output. I hope it is sufficient. Let us know if not suffice.

maheshbabu_0-1684124814350.png

I have taken some random numbers to make you understand, feel free to ask anything you don't understand. Thanks

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

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!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.