Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kadifr2008
Frequent Visitor

How to create a table out of a dynamic visual

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?

1 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 )
    )
)

111.PNG

 

Thanks,
Xi Jin.

View solution in original post

10 REPLIES 10
kadifr2008
Frequent Visitor

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
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



  Capture.PNG

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 inAttrition
00
40
00
00
20
00
00
00
40

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 )
    )
)

111.PNG

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.