Top value

[Jyaulhaq]

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 ?

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-xuxinyi-msft · Accepted solution]

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.

[vs_7]

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]

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