Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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
Solved! Go to Solution.
Hi @Anonymous ,
How about this:
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/
Proud to be a Super User!
Hi @Anonymous ,
How about this:
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/
Proud to be a Super User!
This worked perfectly.
Answering your question: if all my columns are 0, I want "Partial", which of course happens with your formula. Thank you!
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
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!