Forum Discussion

deb_power123's avatar
deb_power123
Icon for Helper V rankHelper V
5 years ago

DAX to handle conditional formatting for column category

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
school1Floor1       28.03.2021      40%
school1Floor2       29.03.2021      20%
school1Floor3       27.03.2021      12%
school2Floor5       29.03.2021      65%
school2Floor2       28.03.2021       18%
school2Floor1       27.03.2021      70%
school2Floor7       25.03.2021      11%
school3Floor1       29.03.2021     35%
school3Floor2       28.03.2021     62%
school3Floor1       29.03.2021     35%
school3Floor2       24.03.2021     62%
school3Floor3       23.03.2021      35%
school3Floor4       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

Need Help
Need Help DAX
Need Help DAX measure

1 Reply

  • Markus_Re's avatar
    Markus_Re
    Icon for Resolver I rankResolver I
    5 years ago

    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!