cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Super User

Hi @Anonymous ,

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 🙂

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

2 REPLIES 2
Super User

Hi @Anonymous ,

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 🙂

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

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!

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors