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.

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
4 years ago

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

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's avatar
    v-yanjiang-msft
    Community Support
    4 years ago

    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