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

hi @Greg_Deckler here is the link to the file with the updates : https://drive.google.com/file/d/0B0XwYvmbIN12REpUVXRGbG1ickU/view?usp=sharing

 

i want to have current status like the way i have formulated historic status. Have a look and it should be clear to you come up with the best solution. Thanks

hi @Greg_Deckler i have removed the circular dependency error but using the formula you suggested, the project status value gets changed when i change date but i want to have the values for 9th may as fixed value. I dont want to change those values whenever i change date using the timeline. Thanks

Where I had the ALL before, try adding that back in but use ALLEXCEPT('Project'[Name]). This will remove the context of your other filters.


@ 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...

Sorry, try this:

 

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

However, this is going to tie you to Using ProjectHistory[Name] in your visualization as the filter. I'll try to see if I can get this to reference Project[Name] instead but still pull ProjectHealth from ProjectHistory. Thought maybe a RELATED would work but no dice. Might have to use a SUMMARIZE.


@ 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 the last formula you provided does not work and give blanks in visualizations. can  you suggest a better alternative? Thanks

@Greg_Deckler i did not notice the recent formulae you suggested. I am trying these out and letting you know shortly. thanks

Hi @Greg_Deckler its not really doing the trick. can you use the history compare table? and also use the column historic status instead of project health. Also, if you can create a calculated column with fixed project status values (USING HISTORIC STATUS COLUMN from history compare table) for all the projects for 9th may, then its going to give the exact result i want. Sorry for changing the specification but this is exactly what i am looking for. Thanks

I can't find a column called historic status.


@ 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 see in this link:  https://drive.google.com/file/d/0B0XwYvmbIN12REpUVXRGbG1ickU/view?usp=sharing

You can find the historic status column in the history compare table. History compare>historic status (that contains the image url)

This is what I am seeing in the HistoryCompare table. There is nothing called Historic Status. There is ProjectHealth. Also, I do not see an image URL. What am I missing?

 

historycompare.png


@ 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 check in this link: https://drive.google.com/file/d/0B0XwYvmbIN12Uzc1Y2pub1hlTjg/view?usp=sharing

this file contains the historic status column in the history compare table. If you could suggest a way to find a solution regarding the last requirement I posted, it will be amazing. I have already bothered you a lot. Looking forward to the desired solution which I believe is not far. Thanks

These three measures seem to work and you put the last measure into a Table visualization along with Project[Name].

 

HistoryCompareLastDate = MAX(HistoryCompare[HistoryDate])

HistoryCompareLastStatusMeasure = TOPN(1,VALUES(HistoryCompare[Historic Status]))

HistoryCompareFinalLastStatus = MAXX(SUMMARIZE(Project,Project[Name],"Last Date",[HistoryCompareLastDate],"Status",[HistoryCompareLastStatusMeasure]),[Status])

 

Now, here is almost your real problem. You can't flag a measure as an Image URL. Therefore, create the last Measure instead as a Calculated Column in Projects table. Or create a calculated column in Projects that is simply:

 

ProjectFinalLastStatus = [HistoryCompareFinalStatus]

But, your real problem is that you are missing a relationship between Projects table and HistoryCompare so you need to add that based on ProjectUid. Once you do that, this will work as described.

 


@ 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 its almost there but slight issue. Whenever I select a date on the timeline, the current status value also gets changed. But i want the 9th may values for project status to stay fixed even if i select other dates on the timeline. the following image should clarify: 

Untitled.png

Here you can see that the value of the current status column is changing. but the current status is the status for 9th may which should be fixed even if i change date on the timeline. This is the final problem i am facing. Thanks a lot

Try this one:

 

HistoryCompareFinalFinalLastStatus = MAXX(SUMMARIZE(Project,Project[Name],"Last Date",CALCULATE([HistoryCompareLastDate],ALLEXCEPT(Project,Project[Name])),"Status",CALCULATE([HistoryCompareLastStatusMeasure],ALLEXCEPT(Project,Project[Name]))),[Status])

@ 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 the issue unfortunately persists. I am afraid, there might not be a proper solution to this

@Greg_Deckler if you could suggest a way to create a calculated column containting the historic status value for the projects for the latest date, it will be great. In short, historic status for the latest datest also happens to be the current status for the projects. So, creating a calculated column named current status is the objective. So, whatever are other dates or values in the history compare table, the current status column will contain only the latest historic status for all the projects. Once that is achieved, i will covert the data category of the current status column to image url to show the image indicators. Hope its clear. Thanks. 

@Greg_Deckler the issue with your last suggested formula is that the project status value gets changed if i change the date from 9th may. I dont want that. Secondly, i want the colored indicators to work as project status. So, using the historic status column should be the key and creating final results as a calcualted column is going to let me use the colored indicators as i would be able to format it as image url . Measure does not let me do that. In short, i need the fixed project status values for 9th may and store those values as a column (such as Apparel ERP: www.blahblah.yellow, (9th may value).....so on)

OK, I might finally have this:

 

Create measures like this:

LastDate = MAX(ProjectHistory[HistoryDate])

LastStatusMeasure = TOPN(1,VALUES(ProjectHistory[ProjectHealth]))

Then create a measure like this:

FinalLastStatus = MAXX(SUMMARIZE(Project,Project[Name],"Last Date",[LastDate],"Status",[LastStatusMeasure]),[Status])

May have to address the ALLEXCEPT issue within this but this way is going to be way more flexible, you just have to create the LastDate and LastStatusMeasures per table you want to use as status health.

 


@ 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 will try these out but I dont want to calculate from the date table,. I want to calculate from the history compare table of the dataset. 

@Greg_Deckler Herei s my requirement again: I want to find the latest date for each of the project and then based on that find the status of the project on that specific day and have those values fixed 

Thanks @Greg_Deckler. What i need to have is the most recent date for each of the project and then extract the status of the project on that specific date and keep it fixed and not have it changed if other parameters are changed using slicers. Thanks

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.