Forum Discussion

Jyaulhaq's avatar
Jyaulhaq
Frequent Visitor
2 years ago
Solved

Top value

Dear Friend,

 

i have two measures: Hours and Day as output data shown below, i want to get Top 1 Hours and Day based on Hours. result for below data: Top1:  56,758 hours and 23 Day

Another example: if i will have data like below, if Day is empty then then Hours=Hours+last above hours

and Day=Day+above Day.

here Day is empty in April so Hours=41000+23371=64371 and Day=23+30, the result table in on right side. then we need to find Top1 based on hours i.e. hours=64371 and Day=53

 

 

Another example:

then Top1 is Hours=13419 and Day 58

 

how can i get using Dax ?

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Jyaulhaq 

     

    Below is an example I created to achieve the effect you want.

    My sample:

     

    1. I created two measures to calculate the hours and days per month

    Hours = CALCULATE(SUM('Table'[Hour]), ALLEXCEPT('Table', 'Table'[Month]))

     

    Days = CALCULATE(SUM('Table'[Day]), ALLEXCEPT('Table', 'Table'[Month]))

     

    2. Create several calculated columns as follow

    rank = RANKX('Table', [Month], , ASC, Dense)

     

    Hours1 = 
VAR _lRank = [rank] - 1
VAR _lhours = MAXX(FILTER('Table', [rank] = _lRank), [Hours])
RETURN
IF([Days] = BLANK(), [Hours] + _lhours, [Hours])

     

    Days1 = 
VAR _monthDays = DAY(EOMONTH([Month], 0))
VAR _lRank = [rank] - 1
VAR _lDays = MAXX(FILTER('Table', [rank] = _lRank), [Days])
RETURN
IF([Days] = BLANK(), _lDays + _monthDays, [Days])

     

    Result:

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    hi  
    try below dax measures

    1.AdjustedHoursAndDay = VAR CurrentHours = 'YourTable'[Hours] VAR CurrentDay = 'YourTable'[Day] VAR PreviousHours = CALCULATE(MAX('YourTable'[Hours]), FILTER('YourTable', 'YourTable'[Day] = EARLIER('YourTable'[Day]) - 1)) VAR PreviousDay = CALCULATE(MAX('YourTable'[Day]), FILTER('YourTable', 'YourTable'[Day] = EARLIER('YourTable'[Day]) - 1)) RETURN IF(ISBLANK(CurrentDay), CurrentHours + PreviousHours, CurrentHours)


    2.

    Top1HoursAndDay = 
VAR TopHours = CALCULATE(MAX('YourTable'[AdjustedHoursAndDay]))
VAR TopDay = CALCULATE(MAX('YourTable'[Day]), FILTER('YourTable', 'YourTable'[AdjustedHoursAndDay] = TopHours))
RETURN
CONCATENATE(TopHours, " hours and ", TopDay, " day")

     

    Jyaulhaq

    • Jyaulhaq's avatar
      Jyaulhaq
      Frequent Visitor
      2 years ago

      i have Hours and Day measures, not column. Please do needful

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Jyaulhaq 

     

    Below is an example I created to achieve the effect you want.

    My sample:

     

    1. I created two measures to calculate the hours and days per month

    Hours = CALCULATE(SUM('Table'[Hour]), ALLEXCEPT('Table', 'Table'[Month]))

     

    Days = CALCULATE(SUM('Table'[Day]), ALLEXCEPT('Table', 'Table'[Month]))

     

    2. Create several calculated columns as follow

    rank = RANKX('Table', [Month], , ASC, Dense)

     

    Hours1 = 
VAR _lRank = [rank] - 1
VAR _lhours = MAXX(FILTER('Table', [rank] = _lRank), [Hours])
RETURN
IF([Days] = BLANK(), [Hours] + _lhours, [Hours])

     

    Days1 = 
VAR _monthDays = DAY(EOMONTH([Month], 0))
VAR _lRank = [rank] - 1
VAR _lDays = MAXX(FILTER('Table', [rank] = _lRank), [Days])
RETURN
IF([Days] = BLANK(), _lDays + _monthDays, [Days])

     

    Result:

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.