cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hasan_iba Helper II
Helper II

Re: Creating Colored Indicators

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

Hasan_iba Helper II
Helper II

Re: Creating Colored Indicators

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

Super User IV
Super User IV

Re: Creating Colored Indicators

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.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: Creating Colored Indicators

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.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super User IV
Super User IV

Re: Creating Colored Indicators

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.

 


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Hasan_iba Helper II
Helper II

Re: Creating Colored Indicators

hi @Greg_Deckler the last formula you provided does not work and give blanks in visualizations. can  you suggest a better alternative? Thanks

Hasan_iba Helper II
Helper II

Re: Creating Colored Indicators

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

Hasan_iba Helper II
Helper II

Re: Creating Colored Indicators

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

Hasan_iba Helper II
Helper II

Re: Creating Colored Indicators

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

Super User IV
Super User IV

Re: Creating Colored Indicators

I can't find a column called historic status.


---------------------------------------

Not the Power BI thought police...

I have NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors