Conditional Formatting of the column cell based on system date

[deb_power123]

Hi All,

I have a table named "LocationSchool " and  another table "Weekcal" which are related via weeknumber as 1:n.

Weekcal table contains weeknumber and weeknumber[total 53 weeks] with week prefix. 

Location school has a calculated column weeknum as below 

weeknum = WEEKNUM([Date]) to calculate the weeknumber and keep the data upto date with systemdate.

As per my requirement , when I select any weeknumber which corresponds to the system date weeknumber i.e today is weeknumber 14 since it is 29.03.2021 as system date.Then it should check the  criteria[if seats occupied > 65% floor should be green, if seats occupied is between 65% to 20%  and color the Floor is Yellow and if seats occupied <=20% then floor  should be red]

 so resultant color formatting should look as below and thisseats occupied  date should be corresponding to the system date of the selected weeknumber.

School1-->Floor 2 eats occupied <= 20% [Red color] 

School2-->Floor 5 seats occupied is between 65% to 20 % [Yellow color]

School3-->Floor 1 seats occupied > 65% [Green color]

 My current  visualization looks as below :

My input data is as below :-

SchoolBuilding Name	Floor	Date	seatsoccupied
school1	Floor1	28.03.2021	40%
school1	Floor2	29.03.2021	20%
school1	Floor3	27.03.2021	12%
school2	Floor5	29.03.2021	65%
school2	Floor2	28.03.2021	18%
school2	Floor1	27.03.2021	70%
school2	Floor7	25.03.2021	11%
school3	Floor1	29.03.2021	35%
school3	Floor2	28.03.2021	62%
school3	Floor1	29.03.2021	35%
school3	Floor2	24.03.2021	62%
school3	Floor3	23.03.2021	35%
school3	Floor4	22.03.2021	62%

MY PBIX FILE IS AS BELOW :-

https://file.io/XRZG4B5eOGYB

Could someone please suggest an approach  , how can i get the color conditional formatting of the floor column corresponding to the system date of the selected weeknumber.

It will be great if we can add some symbols or icons [arrow icons ] and color it within the Floor cell  instread of coloring the whole Floor cell of Floor column. 

The data will be refreshing everyday and you will have the entry of system date or data today for each date .For Eg: Tommorow we will get the seatsoccupied %age value for date "30.03.2021 " and so on.

Kind regards

Sameer

[amitchandak]

@deb_power123 , Try a color measure

Switch(true(),
[seats occupied] <= .2, "Red",
[seats occupied] <= .65, "Yellow",
"Green"
)

Use that in Conditional Formatting -> Advance Option -> Field Value option

refer color measure

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

[deb_power123]

but the floor column is not showing in conditional value option, moreover it doesnt handle the system date condition or max date condition as in my requirement. The above stated doesnt work, could you please see the .dax file i attached above

[v-yiruan-msft · Accepted solution]

Hi @deb_power123 ，

We can only apply conditonal formatting to tables and matrixs, and only to Values fields. I failed to open the file you provided, hence I created a sample pbix file(see attachment) based on the data you provided. Please check if it is what you want.

Best Regards

[deb_power123]

True and I aggree that only matrix values qualify for comditional formatting.Thankyou for your response, it was helpful