Dynamically highlight rows of a table based on the data.

[Learner_SG]

Hi , I have a job requirement in which the power bi has to assess the data based on the last week usage/consumption of the toilet/rooms and recommend the cleaning schedule for the current day. To expalin in detail:(Let's say the Last week usage of male toilet was high during the morning ,then the cleaning should be recommended first in the morning. ( The table has a fixed time of cleaning  schedule, (8 am,11am ,3 pm and 7 pm).If the usage is high in the morning,then the 1st cleaning should be 8 and so on..) Has anyone did previously any DAX related to these type of recommendations? 

[v-zhangti]

Hi, @Learner_SG 

Can you provide a simple PBIX file for testing? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures. I look forward to your response.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Learner_SG]

Hi @v-zhangti  Due to sensitivity of the data nature, I have copied some sample data of the required part and saved it in the excel file.

My current requirement is that based on the occupancy status which is found by (Occupied_status)and water consumption of the urinal(UR water consumption(mL)) and Water Closet (WC Water Consumption(mL)) I need to come up with a recommended cleaning schedule for the male ,female and Handicap toilets.

Currently the condition set is:

For this week Monday, Power BI should check last week Monday’s usage. For Every 1 hour- check for the occupancy count and water usage of the toilets(eg:male). if higher than a limit set(average usage of that particular day) ,then We have to select 4 cleaning schedules which is at least a gap of 2 hours between(8 to 10PM) and display it in the dashboard table .

So, this schedule will change every day based on the corresponding last week same day usage.

I have attached the excel file data. and the sample dashboard.Thanks in advance for the help.

https://1drv.ms/u/s!AspD-EyWu948gi1bwJZbLNQIlW1K?e=DWYK1N

[v-zhangti]

Hi, @Learner_SG 

I can't open your link, would you be so kind as to provide a screenshot of the example data in Excel?

Best Regards

[Learner_SG]

Hi , I am sorry that the link could not be opened.

I have share it in google drive and verified it could be opened. could you check.

 https://drive.google.com/drive/folders/1am2JXiMkOjkvvMFLEx7NQNEj0QwypObP?usp=sharing

[TomMartens]

Hey @Learner_SG ,

please consider creating a pbix file that contains sample data but still reflects your data model (tables, relationships, calculated columns, and measures). Upload the pbix file to onedrive or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method, please share the Excel file as well.

Please explain the expected result based on the sample data you provided.

It's also important that you share the business rules, like how a high usage is defined.

Regards,

Tom

[Learner_SG]

Hi @TomMartens , I am sorry that the link could not be opened.

I have share it in google drive and verified it could be opened. could you check.

 https://drive.google.com/drive/folders/1am2JXiMkOjkvvMFLEx7NQNEj0QwypObP?usp=sharing

[TomMartens]

Hey @Learner_SG ,

please explain the expected result based on the data you provided, also it not clear how to consider the status field, as there are blanks and the value "yes".

What else does a DAX statement has to consider what currently is not in the below matrix visual:

For example, explain your expected result for the 27th of February, and a 2nd day.

Regards,

Tom

Thanks,

Tom

[Learner_SG]

Hi @TomMartens ,

With the matrix visual that you have come up with  is a bit confusing for me to explain.I will try to elaborate more on the data sample below and have removed more columns which maybe misleading and also the blanks.

The column A - displays the occupied status of the toilets based on the time(column B).

                           If "yes" it means occupied at that particular time.

    Column D and F- Consumption amount of water in the Urinal and Water Closet.

   Column K - is the date  

   Column L  - is the hour of the occupany (eg:2nd hour in the 24 hour time)

   Column M- Gender

   Coumn N - is the unit location (WC Glass flush valve F04) -meaning Water closet Female

   Toilet 4.

Based on this data, I need power bi to understand how much is the occuapncy of the toilets for male/female/handicap and also the consumption of water in the urinal/water closet for the 3 type of toilets(male/female/handicap) and when I select each type of toilet(m/f/hcp) in the dashboard, it should show me in the table the recommended cleaning schedule for that particular day. 

I hope this might clarify your doubt and if you need any more info ,please let me know.

Since I am new to Power BI , unable to come up with the DAX for the recommendation table.

Thanks!

have updated the data and removed the misleading the blanks/ misleading.

https://docs.google.com/spreadsheets/d/15N2Vqx1tAGvg6bzLbBaRClUkWMpBlkA9/edit?usp=sharing&ouid=10671...

[TomMartens]

Hey @Learner_SG ,

please describe the expected result based on the data you provided.
It's not necessary to use DAX, instead you can use some prose, like
For the 27th of January I expect the following recommendation

...

because

on the 20th of January the data was this and aggregating the data like that will provide the expected result.

Regards,

Tom

[Learner_SG]

Hi @TomMartens ,

 this will be having real time data collection once implemented.

So what I need is that ,I have to come up with a logic which will be changing each day based on the last week data.

The expected result is that  it has to show the recommended cleaning schedule for 4 timings  (in the dashboard)based on the last week same day occupancy count and water usage for the urinal and water closet.Is it possible to come up with a logic based on the the provided data?

[TomMartens]

Hey @Learner_SG ,

if you have real time streaming data, you have to get familiar with using realtime streaming datasets. The video by Reza Rad shows how can incorporate measure in combination with streaming datasets: Writing calculations on a real time streaming Power BI dataset using DAX measures - YouTube

To answer your question: Yes it's possible, but nevertheless you did not provide the algorithm on how to highlight the schedules.

Regards,
Tom

[Learner_SG]

Hi @TomMartens ,thank u  for the reply.  I need to come up with an alogirthm as nothing else is provided from the client side and I am clueless how to proceed with the informations that I mentioned in the previous msgs. 

[Learner_SG]

Hi  @TomMartens ,sorry for the delay in sendin the data. Due to sensitivity of the data nature, I have copied some sample data of the required part and saved it in the excel file.

My current requirement is that based on the occupancy status which is found by (Occupied_status)and water consumption of the urinal(UR water consumption(mL)) and Water Closet (WC Water Consumption(mL)) I need to come up with a recommended cleaning schedule for the male ,female and Handicap toilets.

Currently the condition set is:

For this week Monday, Power BI should check last week Monday’s usage. For Every 1 hour- check for the occupancy count and water usage of the toilets(eg:male). if higher than a limit set(average usage of that particular day) ,then We have to select 4 cleaning schedules which is at least a gap of 2 hours between(8 to 10PM) and display it in the dashboard table .

So, this schedule will change every day based on the corresponding last week same day usage.

I have attached the excel file data. and the sample dashboard.Thanks in advance for the help.

https://1drv.ms/u/s!AspD-EyWu948gi1bwJZbLNQIlW1K?e=DWYK1N

[Learner_SG]

Hi @TomMartens , thanks for the reply. I am in the midst of collecting a few more infos from the client regarding these issues. Will get back to u soon.