Generate new column based on row values

Question

I have a table with the following columnsCaseNumber&nbsp; &nbsp; Elect&nbsp; &nbsp; Gas&nbsp; &nbsp; Sewer&nbsp; &nbsp;Trash&nbsp; Water&nbsp;Elect, Gas, Sewer, Trash, and Water; they all have 0 as the default value. Each CaseNumber has these 5 fields.Example:CaseNumber&nbsp; &nbsp; Elect&nbsp; &nbsp; Gas&nbsp; &nbsp; Sewer&nbsp; &nbsp;Trash&nbsp; Water12345&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; T&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; S12346&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; T&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S&nbsp; &nbsp; &nbsp; &nbsp; S&nbsp;I want a formula that returns a column (UtilityStatus) if the following conditions are met:Pseudocode:If(Elect,Gas, Sewer, Trash &amp; Water are all diff from 0, Complete, Partial)&nbsp;Therefore, following examples above, this is the result I shoul get.&nbsp;CaseNumber&nbsp; &nbsp; Elect&nbsp; &nbsp; Gas&nbsp; &nbsp; Sewer&nbsp; &nbsp;Trash&nbsp; Water&nbsp; UtilityStatus12345&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; T&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; S&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Partial12346&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;S&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; T&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; S&nbsp; &nbsp; &nbsp; &nbsp; S&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Complete&nbsp;&nbsp;&nbsp;&nbsp;

tackytechtom · Accepted Answer

Hi&nbsp;Anonymous&nbsp;,&nbsp;How about this:&nbsp;&nbsp;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"
)&nbsp;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 🙂&nbsp;&nbsp;Let me know if this helps 🙂&nbsp;/Tomhttps://www.tackytech.blog/https://www.instagram.com/tackytechtom/

Forum Discussion

Generate new column based on row values

2 Replies

Related content

Splitting Column values

Generating Missing Data

SUMMARIZE, VALUES, and other table generation/manipuation with field parameters

Matching Column Values

List Count of each value in list Accumulate v Generate