Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Jyaulhaq
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

Jyaulhaq_1-1712132387668.png

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

Jyaulhaq_5-1712134058843.png

 

 

Another example:

Jyaulhaq_6-1712134170269.png

then Top1 is Hours=13419 and Day 58

 

how can i get using Dax ?

 

 

 

 

 

1 ACCEPTED SOLUTION
v-xuxinyi-msft
Community Support
Community Support

Hi @Jyaulhaq 

 

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

My sample:

vxuxinyimsft_0-1712282406282.png

 

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:

vxuxinyimsft_1-1712282629156.png

 

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.

View solution in original post

4 REPLIES 4
Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-xuxinyi-msft
Community Support
Community Support

Hi @Jyaulhaq 

 

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

My sample:

vxuxinyimsft_0-1712282406282.png

 

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:

vxuxinyimsft_1-1712282629156.png

 

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
Responsive Resident
Responsive Resident

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
Frequent Visitor

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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