Forum Discussion
Power BI DAX
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 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 _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-yanjiang-msft , Thanks a lot. I did a quick check,it works,
Hi v-yanjiang-msft ,In continuation to this query, If I need to modify to select the 4 highest timings but need to distribute it evenly between 7AM to 10PM ,any suggestions on how to modify the query . The idea in my mind is to split into 4 sessions 7-10AM 11-2 PM 3-6PM 7-10 PM and select the highest from each one. But the catch is that if the highest is 10 AM for the 1st session ,then it should not select the 2nd session timing as 11 am since it would be too close.Could advise?
Hi v-yanjiang-msft , could I clear a doubt which went unnoticed at that time .The rank function does not seem to work well. It shows all the data in the table with all of them having rank1. I was unable to resolve it. could help?
Hi Learner_SG ,
As in the formula, TODAY()-7 equals to 3/10/2022, which has no data in your sample, so all the rank return 1. As far as your screenshots are concerned, modify the TODAY()-7 to TODAY()-20, it will work.
Best Regards,
Community Support Team _ kalyjok v-yanjiang-msft , I did check on the sample file that you had sent ,it works. but not for my original file. i tried to look at the syntax and anything else that could go wrong .
also , just one more doubt to ask , if I need to select 2 days , that means day -7 and day-6 , how should I modify the dax?