cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

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.

3 REPLIES 3
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 ()
)

Regular Visitor

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.

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors