Forum Discussion
Power BI DAX
Hi ,Could anyone help me this DAX.
| Date | Hours | Usage | Occupancy |
| 27-Feb | 2 | 5 | 1 |
| 27-Feb | 4 | 7 | 2 |
| 27-Feb | 7 | 8 | 3 |
| 27-Feb | 3 | 3 | 1 |
| 27-Feb | 9 | 2 | 1 |
| 27-Feb | 5 | 4 | 2 |
| 27-Feb | 7 | 1 | 6 |
| 27-Feb | 3 | 6 | 5 |
| 27-Feb | 8 | 1 | 3 |
| 27-Feb | 10 | 3 | 2 |
| 26-Feb | 12 | 4 | 1 |
| 26-Feb | 14 | 6 | 1 |
| 26-Feb | 6 | 1 | 4 |
| 26-Feb | 8 | 1 | 6 |
| 26-Feb | 10 | 2 | 4 |
| 26-Feb | 1 | 4 | 2 |
| 26-Feb | 3 | 3 | 1 |
| 26-Feb | 13 | 7 | 3 |
Based on this data, I need to write a DAX to select the 4 highest times for each day when the usage+occupancy is high.Could anyone help?
Hi Learner_SG ，
I got it. Here's my solution.
1.Create a check measure.
Check = IF(MAX('Sheet4'[Parsed_Date])=TODAY()-7,1)Put the measure in the visual filter and let the value is 1.
2. Create a rank measure.
Rank = RANKX ( FILTER ( ALL ( 'Sheet4' ), 'Sheet4'[Parsed_Date] = TODAY () - 7 ), CALCULATE ( SUM ( 'Sheet4'[WC Water Consumption(mL)] ) ), , DESC, DENSE )Put the measure in the visual filter and let the value <=4, get the correct result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
22 Replies
Hi Learner_SG ,
do you mean display the 4 highest times in the last 7 days? If this is the case, here's my solution.
1.Create a date table.
Date = CALENDAR(DATE(2022,1,1),TODAY())2.Make relationship between the two tables, note the Parsed date should be Date type.
3.Put the date column in a slicer, select Relative Date>Last 7 Days.
4.In the visual filter, put WC Water Consumption in the box below "By value", and select Top 4.
It will always get the top 4 values in the last 7 days.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi v-yanjiang-msft , thanks for the detailed explanation and solution. In fact my requirement is that , I need to display in the table , previous 7th day data. that means, if i display the dashboard today(friday), it should show me the last friday's 4 highest time when the usage+occupancy is high and it will change each day. Hope its clear now. Sorry,if my earlier phrasing of the sentence caused confusion. Could you guide me on this?
Hi Learner_SG ，
I got it. Here's my solution.
1.Create a check measure.
Check = IF(MAX('Sheet4'[Parsed_Date])=TODAY()-7,1)Put the measure in the visual filter and let the value is 1.
2. Create a rank measure.
Rank = RANKX ( FILTER ( ALL ( 'Sheet4' ), 'Sheet4'[Parsed_Date] = TODAY () - 7 ), CALCULATE ( SUM ( 'Sheet4'[WC Water Consumption(mL)] ) ), , DESC, DENSE )Put the measure in the visual filter and let the value <=4, get the correct result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Can you try below measure and use it as visual filter please?
rank =RANKX(ALLEXCEPT('Table','Table'[Date]),[usage_occ],,DESC,Skip)Hi coskuersanli , I am still unable to get the solution. Actually , I had shared the data for another query to you.If you have time , could u guide me with the DAX.
https://drive.google.com/file/d/1rgIn7gN7Kuux8trVMtPIj8qxxulUVYLL/view?usp=sharing.
Hi Learner_SG ,
Dates and hours are unique in sample file. I can help if you send me some data like in your first post 🙂
