March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I have a problem, i have create a visual table with a conditional formating and now i am trying to create another table out of the previous visual, by counting how many are red, green etc... can BI do that?
Solved! Go to Solution.
Hi @kadifr2008,
Check this:
Create a new table with expression:
Count Color Table = SELECTCOLUMNS ( FormatColor, "Call in Red", CALCULATE ( COUNT ( FormatColor[Call in] ), FILTER ( FormatColor, FormatColor[Call in] > 0 ) ), "Call in Green", CALCULATE ( COUNT ( FormatColor[Call in] ), FILTER ( FormatColor, FormatColor[Call in] = 0 ) ), "Attrition Red", CALCULATE ( COUNT ( FormatColor[Attrition] ), FILTER ( FormatColor, FormatColor[Attrition] > 0 ) ), "Attrition Green", CALCULATE ( COUNT ( FormatColor[Attrition] ), FILTER ( FormatColor, FormatColor[Attrition] = 0 ) ) )
Thanks,
Xi Jin.
Here is the condition:
i am unsing the if statement:
column=if(sheet1[count)<=13,"red","Green), i have so blank row, and the formula is counting that as red, and i dont want that, how to make my formula ignore the blanck row. thank you
Hi @kadifr2008,
You cannot count how many red, green there are but you can create a measure that calculates how many values are within the range you have in that way it will calculate what you want so red/green.
If you add a sample of your data and the conditional formatting settings I can help you better.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Português
this my data, i would like count like PTO at risk, i have 2 yellow
for example i have for the monthly call in , if it is more than 1 it is yellow, if it 0 then it is green
Hi @kadifr2008,
I'm not quite understand about your requirement.
According to your screenshot, there're two values in PTO as risk, 4 and 0. And they are all yellow. However as you said that "if it is more than 1 it is yellow, if it 0 then it is green". Which one is right?
Then generally, if you need to count rows based on conditions. You can try following expression.
Count of yellow = CALCULATE ( COUNT ( 'table'[PTO at risk] ), FILTER ( 'table', 'table'[PTO at risk] >= 1 ) )
Since I don't know your source table structure and the data. I'm not sure if it will work for you. Thereby, please share us more detailed information about your requirement. Like some sample data which we can copy and paste directly and its corresponding desired result. Or your pbix file with One Drive or Google Drive. Screenshot helps nothing.
Thanks,
Xi Jin.
Call in | Attrition |
0 | 0 |
4 | 0 |
0 | 0 |
0 | 0 |
2 | 0 |
0 | 0 |
0 | 0 |
0 | 0 |
4 | 0 |
for example for the monthly call in , if 0= green
if >0= green
for attrition if 0=green
if more than 0 = red
thank you for your reply
so i made a table out of that, then i put my condition on the table.
now i have to create a table and do the count of how many is red and how many is green for the manager to see, so they know where to focus.
Hi @kadifr2008,
Check this:
Create a new table with expression:
Count Color Table = SELECTCOLUMNS ( FormatColor, "Call in Red", CALCULATE ( COUNT ( FormatColor[Call in] ), FILTER ( FormatColor, FormatColor[Call in] > 0 ) ), "Call in Green", CALCULATE ( COUNT ( FormatColor[Call in] ), FILTER ( FormatColor, FormatColor[Call in] = 0 ) ), "Attrition Red", CALCULATE ( COUNT ( FormatColor[Attrition] ), FILTER ( FormatColor, FormatColor[Attrition] > 0 ) ), "Attrition Green", CALCULATE ( COUNT ( FormatColor[Attrition] ), FILTER ( FormatColor, FormatColor[Attrition] = 0 ) ) )
Thanks,
Xi Jin.
Thank you for the reply, have you created a new table called FormatColor, because when i tried it is telling me table called formatcolor not found
Hi @kadifr2008,
The FormatColor table is my test source table. You should change it with your own table.
Thanks,
Xi Jin.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
91 | |
86 | |
77 | |
49 |
User | Count |
---|---|
164 | |
149 | |
101 | |
73 | |
56 |