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

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?

  • Learner_SG's avatar
    Learner_SG
    Helper IV
    4 years ago

    https://drive.google.com/file/d/1Q5junFvjWLbvJrfjjt5nBEriKkqQlEgM/view?usp=sharing

     

    Hi coskuersanli  i had tried with both parsed and extr_date. both gave the same result.

    Inface parsed date was giving error for some dates, ie 1 day ahead  for some dates. That's why I used this extr_date. Anyways , I have attached the sample updated file link. Also 1 more doubt, Do you have idea how I can select the top4 of the last week , ie  today when I display , I need to show the last wednesday's result.So the table which I attaches the result should show the 4 results of this one for each day .If you could create the measure , which includes that one , it would be great.I am totally lost at this part.tks.

  • Learner_SG's avatar
    Learner_SG
    Helper IV
    4 years ago

    coskuersanli ,Good Day! were you able to check?any updates?