Power BI DAX

[Learner_SG]

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?

[v-yanjiang-msft]

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 _ kalyj

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-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?

[v-yanjiang-msft · Accepted solution]

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.

[Learner_SG]

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?

[v-yanjiang-msft]

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 _ kalyj

[Learner_SG]

ok @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? 

[v-yanjiang-msft]

Hi @Learner_SG ,

If you need to select 2 days, you can modify the date part in the formula like this:

'Sheet4'[Parsed_Date] >= TODAY () - 7&&'Sheet4'[Parsed_Date] <= TODAY () - 6

Best Regards,
Community Support Team _ kalyj

[Learner_SG]

hi @v-yanjiang-msft , thanks for the help. I would like to ask for one more favour  which I had highlighted yesterday. My rank measure seems to be not working . I checked the dates  which seems to be correct. below is the screenshot. with all the ranks showing 1.is it possible for you to find what might be the error at a glance?

[v-yanjiang-msft]

Hi @Learner_SG ,

In your formula, for the below condition, there will return no date, as no date will be both 2022/3/9 and 2022/3/10.

Modify it to

'measurement'[Parsed_Date]>=TODAY()-9 && 'measurement'[Parsed_Date]<=TODAY()-8

Best Regards,
Community Support Team _ kalyj

[Learner_SG]

@v-yanjiang-msft , Thanks a lot.  I did a quick check,it works,

[Learner_SG]

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?

[coskuersanli]

Hi,

Can you try below measure and use it as visual filter please?

rank =

RANKX

(

ALLEXCEPT('Table','Table'[Date]),

[usage_occ],

,

DESC,

Skip

)

[Learner_SG]

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.

[coskuersanli]

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 🙂

[Learner_SG]

Hi @coskuersanli  , Because the data is captured on a real time basis the difference between the times are very close. I have attached another file with more data .

https://drive.google.com/file/d/1O8ohw7KfZ6fealUfS0iyeDd4SLnsSTBO/view?usp=sharing.

what I need to come up , is based on the WC water consumption data  I need to get the highest 4 times( the hours) on each day and later I need to display it in a table. Appreciate the help.tks.

[coskuersanli]

Hi @Learner_SG ,

I've tried below one. But we need to change data type of WC Water Consumption to number. Can you change data type and share with me if it doesn't work, please?

rank =

RANKX

(

ALLEXCEPT('Sheet1',Sheet1[Date]),

SUM(Sheet1[WC Water Consumption(mL)]),

,

DESC,

Skip

)

[Learner_SG]

@coskuersanli , unable to proceed with the given dax. 

[coskuersanli]

Hi @Learner_SG ,

You need to put a comma before DESC 🙂 (,DESC,)

[Learner_SG]

Thanks @coskuersanli , when I try to display the measure, it shows only rank1  for every row.Is it that I am getting wrong any where?

Because , I need to select the highest 4 (hours) of previous 7th day to display it as today's cleaning time.

[coskuersanli]

We are using extr_date in measure, if we change it to parsed date it may work. Can you send me updated sample file if it doesn't work please?