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
Hasan_iba
Helper II
Helper II

Creating Colored Indicators

Hi Power BI experts. Hope you are all doing great. Recently, I am facing an issue with creating colored indicators as per the texts of a specific column. The issue I have is best summarized as follows: 

 

I have an application that collects project management data and snapshots it on a regular basis.  Our customers want to see reports that show how data changes over time.  If you look at this report on our website: https://www.shareadvance.com/report/?rpt=Project-Historical-Comparison that gives you an idea of what we need in this report.  That report compares numerical data, and you can see the current value next to a historical value.  You can change the date for the historical value using the slider.

The report we want to see will be based on data showing Projects and their health status.  The data is stored in text format, but the report needs to show this as graphical indicators.  The report will show historical health status based on a date chosen by the user.  Next to that it will show current health status based on the most recent snapshot like the following image: 

a.png

Here is the link to the pbix file I have : https://drive.google.com/file/d/0B0XwYvmbIN12REpUVXRGbG1ickU/view?usp=sharing

 

Is there a way to achieve what I am looking for? A prompt solution will be highly appreciated. Thanks

Hasan

 

 

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

Store your indicators in a table with the URL to their image indicators:

 

Indicators

Indicator,IndicatorURL

Red,http://www.blahblahblah.com/red.jpg

Yellow,http://www.blahblahblah.com/yellow.jpg

Green,http://www.blahblahblah.com/green.jpg

 

Mark the image url as an Image Url in the data model. 

 

Relate your project status to this table on Indicator. Display the IndicatorURL in your table visualization.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

41 REPLIES 41
Greg_Deckler
Super User
Super User

Store your indicators in a table with the URL to their image indicators:

 

Indicators

Indicator,IndicatorURL

Red,http://www.blahblahblah.com/red.jpg

Yellow,http://www.blahblahblah.com/yellow.jpg

Green,http://www.blahblahblah.com/green.jpg

 

Mark the image url as an Image Url in the data model. 

 

Relate your project status to this table on Indicator. Display the IndicatorURL in your table visualization.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I cannot seem to get the images to show.

 

Tried using PNGs and JPGs.

 

Shared URL location is set to be visible to anyone using the link.No image.png

Manage to get it to work with a link to a Wikipedia public image.

 

Noticed that the image public URL for the Wikipedia one ends with the actual image filename and file extension.

 

The generated shared links for my OD4B do not.

Is this relevant?

Anonymous
Not applicable

Can we use local path for images??

Perhaps if you put it in the correct format, "file:///" but that would not work if publishing to the Service. In addition, if you shared the PBIX file with someone, they would have to have access to that file location or have those files in the same spot as you.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler Would you please suggest how can i Mark the image url as image url? Thanks. 

Go into your data model (middle icon on the left) in Desktop. Click your column. Click "Modeling" tab in the ribbon. In the ribbon in the Properties area you will see Data Category. Drop down and click Image URL.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler thanks a lot for the solutions. I need one more suggestion. I also need to find the current status of projects based on the status of the most recent date. How can i achieve this? Thanks again

Maybe something like:

 

CALCULATE(VALUES([Status]),FILTER(Table,MAX([Date])))

Let me check if this will work


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Eh, kind of close, try this:

 

Measure = CALCULATE(TOPN(1,VALUES('Calendar'[Month Name])),FILTER('Calendar',[Date]=MAX([Date])))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Whoops, didn't see that latest requirement, try this:

 

Measure = CALCULATE(TOPN(1,VALUES('Calendar'[Month Name])),ALL('Calendar'),FILTER('Calendar',[Date]=MAX([Date])))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

hi @Greg_Deckler, is there any way i can achieve it? I dont seem to have finding a solution. 

I think in order to get you a solution I'm going to need to see the data you are working with.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

hi @Greg_Deckler here is the link. I need to find the project health status for each of the project on 9th may which happens to be the latest date for each of the project. Thanks

https://drive.google.com/file/d/0B0XwYvmbIN12REpUVXRGbG1ickU/view?usp=sharing

I believe this:

 

Latest Status = CALCULATE(TOPN(1,VALUES('ProjectHistory'[ProjectHealth])),FILTER(ALL('ProjectHistory'),[HistoryDate]=MAX([HistoryDate])))

Put that into a table along with your project Name.

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

hi @Greg_Deckler i feel like i am almost there but there is an issue. I am getting green as the project status for all the projects but the status for apparel should be yellow, for protective padding , it should be red. but getting green for all. Can you suggest whats going on here? also i need to use the history compare table. 

hi @Greg_Deckler like i mentioned earlier, i want to get the project status for the latest date but i am getting green for all projects for the latest day which is not the correct one. Your expert suggestion regarding the issue will be highly appreciated. Thanks

Sorry, had an errant ALL in there:

 

Latest Status = CALCULATE(TOPN(1,VALUES('ProjectHistory'[ProjectHealth])),FILTER('ProjectHistory',[HistoryDate]=MAX([HistoryDate])))

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler now i am getting circular dependency error. here is the snap: 

Untitled.png

 

I am slowly getting there i feel. Need your expert tricks to get me out of this. Thanks a lot again. 

I'm not getting that error and I have both Latest Status and this new status as measures and in the same table visualization along with project Name:

 

Latest Status History = CALCULATE(TOPN(1,VALUES('HistoryCompare'[ProjectHealth])),FILTER('HistoryCompare',[HistoryDate]=MAX([HistoryDate])))

??


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.