DAX to handle conditional formatting for column category

[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]

Expected output in pbix[this i did to showcase in excel, i need to get this in pbix] :-

Is it feasible?Please suggest

 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 cells in the floor column corresponding to the system date of the selected weeknumber.[in this case we selected week 14 since today's date lies in this week number[

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

[Markus_Re]

Hi @deb_power123,

for your purpose i would switch from Matrix to a basic table. Just by changing the visual, PowerBI automatically created the table described above.  

For formatting you could try this:

color = 

var _sum = SUM(LocationSchool[seatsoccupied])
var _today = TODAY()
var _selecteddate = SELECTEDVALUE(LocationSchool[Date])
var _result = 
IF(HASONEVALUE(LocationSchool[Date]), 
IF(NOT(ISBLANK(_sum)) && _selecteddate = _today,
 SWITCH(TRUE,
 _sum <= 0.2, "red",
 0.2 < _sum && _sum <= 0.65, "yellow",
 0.65 < _sum, "green"), BLANK()), BLANK())

RETURN
_result

After creating the Measure you can use it in the visual options under "Conditional Formatting" -> "Font Color" (or whatever you want to change) -> "Advancend Controls" (If the window doesn´t open from itself) -> Change "Format by" to "Field value" -> under "Based on field" select your previously created (in this case "color") Measure

Hope this helps.

Best regards,

Markus

Did this answer your question? Mark my post as a solution!