Forum Discussion
Helper Isum row from selected columns with condition
Hello,
I'am working on a powerbi report based on user input thru Powerapps wich is stored at Dataverse.
I have multiple projects with multiple tasks where user can input status for the specific task. I want to summerize the different task columns and get the status. So in excel that wolud look something like this:
[IF(CountIF(Column_Names="Red"),"Red",IF(Column_Names="Yellow"),"Yellow",IF(Column_Names="Green"),"Green","Black")
So if one cell is red then return red and so on. Thats because i have alot of tasks and i want to get an quick overview of my projects.
I want to do somthing similar with PowerBI.
This is my Columns and the marked are represent different fases and i want to sumerize the status for the diffrent fases. ex fas0 green, fas1 yellow etc.
Please tell me what more information i need to provide and if there is something i can clearify.
Tahnks in advanced
Hi Gaith47,
Please can you try to create a calculated column, using SWITCH(), TRUE() and the OR conditions, something on the lines of this:
SWITCH(
TRUE(),
COLUMN1="RED" || COLUMN 2 = "RED" || COLUMN X ="RED, "RED",
COLUMN1="Yellow" || COLUMN 2 = "Yellow" || COLUMN X ="RED, "Yellow",
..
BLANK()
)Since the evalution of the SWITCH() statement stops as soon as the first result is obtained, the rest of the code will not be evaluated and you will get the color based on the values of all the necessary columns.
So if none of the columns have Red, then the conditions for Yellow will be evaluated, so on and so forth, and you can specify a default value if none of the colors are there, which in my example is blank.
I hope I have understood the problem and my solution helps you out and if it does, then please do mark it as the solution and/or Kudo it, so that other can reach the solution faster.
Thank you,
Vishesh Jain
10 Replies
Hi,
I think this would be one way to solve your issue:Color =
var Variable1 = IF(SELECTEDVALUE('TABLE'[COLUMN]) = "Red", True, False)
var Variable2= IF(SELECTEDVALUE('TABLE'[COLUMN]) = "Yellow", True, False)
var Variable3= IF(SELECTEDVALUE('TABLE'[COLUMN]) = "Green", True, False)RETURN
SWITCH(
TRUE(),
Variable1 , "Red",
Variable2, "Yellow",
Variable3, "Green")
Gaith47but this will only work for one column? it dosen't check for all columns right?
- mh2587
Super User
https://community.powerbi.com/t5/Desktop/Column-Containing-Values/m-p/1557625
This might help you create a calculated column then try coloring measure
- Gaith47
please see my reply i tride to make it more clear what i want to achive
- Gaith47
So i chose to show my dataverse table because it's mor clean.
So as you can see i have a row for each department and the status is then presentet under each activity. so what i want to do is to get the overall status for each department with a condition. So if the there is any red task then the status will be red. And if there is no red then ill check for yellow and so on.
The next step in a diffrent column sumerize for all the different departments for a specific project. is that possible?
Rött=Red
Gult=YellowGrönt=Green
Svart=Black
- Gaith47
is there any more information i need to provide?
- visheshjain
Impactful Individual
Hi Gaith47,
Please can you try to create a calculated column, using SWITCH(), TRUE() and the OR conditions, something on the lines of this:
SWITCH(
TRUE(),
COLUMN1="RED" || COLUMN 2 = "RED" || COLUMN X ="RED, "RED",
COLUMN1="Yellow" || COLUMN 2 = "Yellow" || COLUMN X ="RED, "Yellow",
..
BLANK()
)Since the evalution of the SWITCH() statement stops as soon as the first result is obtained, the rest of the code will not be evaluated and you will get the color based on the values of all the necessary columns.
So if none of the columns have Red, then the conditions for Yellow will be evaluated, so on and so forth, and you can specify a default value if none of the colors are there, which in my example is blank.
I hope I have understood the problem and my solution helps you out and if it does, then please do mark it as the solution and/or Kudo it, so that other can reach the solution faster.
Thank you,
Vishesh Jain
- Gaith47
Thanks it worked.
