cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 ?

1 ACCEPTED SOLUTION
Community Support

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 4
Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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.

Continued Contributor

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 TopDay = CALCULATE(MAX('YourTable'[Day]), FILTER('YourTable', 'YourTable'[AdjustedHoursAndDay] = TopHours))
RETURN
CONCATENATE(TopHours, " hours and ", TopDay, " day")```

@Jyaulhaq

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors