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.

      • coskuersanli's avatar
        coskuersanli
        Resolver III
        4 years ago

        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?