cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Generate new column based on row values

I have a table with the following columns

CaseNumber    Elect    Gas    Sewer   Trash  Water

 

Elect, Gas, Sewer, Trash, and Water; they all have 0 as the default value. Each CaseNumber has these 5 fields.

Example:

CaseNumber    Elect    Gas    Sewer   Trash  Water

12345                 S          T          A          0        S

12346                 S          T          A          S        S

 

I want a formula that returns a column (UtilityStatus) if the following conditions are met:

Pseudocode:

If(Elect,Gas, Sewer, Trash & Water are all diff from 0, Complete, Partial)

 

Therefore, following examples above, this is the result I shoul get.

 

CaseNumber    Elect    Gas    Sewer   Trash  Water  UtilityStatus

12345                 S          T          A          0        S          Partial

12346                 S          T          A          S        S          Complete

 

 

 

 

1 ACCEPTED SOLUTION
tackytechtom
Super User
Super User

Hi @Anonymous ,

 

How about this:

tomfox_0-1652897516696.png

 

 

Here the DAX for the calculated coumn =

UtilityStatus = 
SWITCH (
    TRUE(),
    Table[Elect] = "0", "Partial",
    Table[Gas]   = "0", "Partial",
    Table[Sewer] = "0", "Partial",
    Table[Trash] = "0", "Partial",
    Table[Water] = "0", "Partial",
    "Complete"
)

 

My first question was, what you wanna return if all columns are 0, but that might not even happen in your data. So, I was just stubbornly following your requirement 🙂 

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
tackytechtom
Super User
Super User

Hi @Anonymous ,

 

How about this:

tomfox_0-1652897516696.png

 

 

Here the DAX for the calculated coumn =

UtilityStatus = 
SWITCH (
    TRUE(),
    Table[Elect] = "0", "Partial",
    Table[Gas]   = "0", "Partial",
    Table[Sewer] = "0", "Partial",
    Table[Trash] = "0", "Partial",
    Table[Water] = "0", "Partial",
    "Complete"
)

 

My first question was, what you wanna return if all columns are 0, but that might not even happen in your data. So, I was just stubbornly following your requirement 🙂 

 

Let me know if this helps 🙂

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

This worked perfectly.

Answering your question: if all my columns are 0, I want "Partial", which of course happens with your formula. Thank you!

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors