How to create a table out of a dynamic visual

[kadifr2008]

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?

[kadifr2008]

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

[MFelix]

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

[kadifr2008]

 

this my data, i would like count like PTO at risk, i have 2 yellow

[kadifr2008]

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

[v-xjiin-msft]

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.

[kadifr2008]

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

[kadifr2008]

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.

[v-xjiin-msft · Accepted 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.

[kadifr2008]

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

[v-xjiin-msft]

Hi @kadifr2008,

The FormatColor table is my test source table. You should change it with your own table.

Thanks,
Xi Jin.