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.

coskuersanli's avatar
coskuersanli
Resolver III
4 years ago

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

     

    coskuersanli , unable to proceed with the given dax. 

    • coskuersanli's avatar
      coskuersanli
      Resolver III
      4 years ago

      Hi Learner_SG ,

       

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

      • Learner_SG's avatar
        Learner_SG
        Helper IV
        4 years ago

        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.