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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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

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
Europe Fabric Conference

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel1

Power BI Monthly Update - May 2024

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