Creating a Status bar|| Help!

[jen8080]

Hello- 

This seems like it should be very simple with a helper table and a couple measures but I am not having any luck.

Goal:

Create a status bar and show the previous statuses were met.

Here is what it should look like:

Status order: 

Status	Sort order
Identified	1
Defined	2
Extracted	3
Formatted	4
Approved	5
Transferred	6
N/A	7

Data:

Company	Country	Product	Status	Status Date
Company Name 1	Spain	Product 1	Approved	1/1/2021
Company Name 1	Italy	Product 2	Transferred	1/2/2021
Company Name 1	France	Product 3	Transferred	1/3/2021
Company Name 1	UK	Product 4	Transferred	1/4/2021
Company Name 1	Netherlands	Product 5	Transferred	1/5/2021
Company Name 1	Montenegro	Product 6	Transferred	1/6/2021
Company Name 1	Croatia	Product 7	Transferred	1/7/2021
Company Name 1	Spain	Product 8	Transferred	1/8/2021
Company Name 1	Italy	Product 9	Transferred	1/9/2021
Company Name 1	France	Product 10	Transferred	1/10/2021
Company Name 1	UK	Product 11	Transferred	1/11/2021
Company Name 1	Netherlands	Product 12	Transferred	1/12/2021
Company Name 1	Montenegro	Product 13	Approved	1/13/2021
Company Name 1	Croatia	Product 14	Transferred	1/14/2021
Company Name 2	Australia	Product 15	Transferred	1/15/2021
Company Name 2	New Zealand	Product 16	Transferred	1/16/2021
Company Name 2	Fiji	Product 17	Formatted	1/17/2021
Company Name 2	Samoa	Product 18	Extracted	1/18/2021
Company Name 2	Tonga	Product 19	Extracted	1/19/2021
Company Name 2	Australia	Product 20	Extracted	1/20/2021
Company Name 2	New Zealand	Product 21	Extracted	1/21/2021
Company Name 2	Fiji	Product 22	Transferred	1/22/2021
Company Name 2	Samoa	Product 23	Transferred	1/23/2021
Company Name 2	Tonga	Product 24	Transferred	1/24/2021

[Anonymous]

Hi @jen8080 ,

How do you get the previous statuses? According to your sample data, the max of status of Company2 Fiji should be Transferred so the previous status should be Approved. But in the expected visual you have provided it's previous status is Formatted? Did I missed some points?

Sort order = LOOKUPVALUE('Status order'[Sort order],'Status order'[Status],Data[Status])

max_status = CALCULATE(MAX(Data[Sort order]),FILTER(ALLEXCEPT(Data,Data[Company],Data[Country]),Data[Status Date]=CALCULATE(MAX(Data[Status Date]),ALLEXCEPT(Data,Data[Company],Data[Country]))))

Best Regards,

Jay

[amitchandak]

@jen8080 , if you looking for status is met the create a color measure

Switch( True(),

not(isblank(max(Table[status]) )), "Blue",

"White"

)

Use this measure using conditional formatting with "Field value" option , for both text and background

refer for steps

https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[jen8080]

@amitchandak 

Thanks- its close, it give the "blue" color to the current status but it isn't showing "blue" for previous statuses.