Forum Discussion

Learner_SG's avatar
Learner_SG
Helper IV
4 years ago
Solved

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 ...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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's avatar
Learner_SG
Helper IV
4 years ago

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?