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 ，

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

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

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