cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kadifr2008 Frequent Visitor
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

Accepted Solutions
v-xjiin-msft Super Contributor
Super Contributor

Re: How to create a table out of a dynamic visual

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

Re: How to create a table out of a dynamic visual

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



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

Proud to be a Datanaut!




kadifr2008 Frequent Visitor
Frequent Visitor

Re: How to create a table out of a dynamic visual

  Capture.PNG

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

kadifr2008 Frequent Visitor
Frequent Visitor

Re: How to create a table out of a dynamic visual

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

Re: How to create a table out of a dynamic visual

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 Frequent Visitor
Frequent Visitor

Re: How to create a table out of a dynamic visual

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

kadifr2008 Frequent Visitor
Frequent Visitor

Re: How to create a table out of a dynamic visual

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

Re: How to create a table out of a dynamic visual

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

kadifr2008 Frequent Visitor
Frequent Visitor

Re: How to create a table out of a dynamic visual

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

Re: How to create a table out of a dynamic visual

Hi @kadifr2008,

 

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

 

Thanks,
Xi Jin.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 182 members 1,809 guests
Please welcome our newest community members: