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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
shareezsaleem
Helper III
Helper III

Get the latest & most repeated Unit Prices

Hi All, I am looking to extract latest value and most repeated value (if tie get max).

 

Item UOM Unit Price Order Date
Item1 EA 95.00 25-Jan-22
Item1 Carton 1,800.00 24-Feb-22
Item1 EA 95.00 19-Feb-22
Item1 EA 80.00 1-Mar-22

 

Result should be like this:

Item UOM Latest Unit Price Most Repeated
Item1 EA 80 95

 

Looking for dax meaure not calculated column measure.

1 ACCEPTED SOLUTION
ddpl
Solution Sage
Solution Sage

@shareezsaleem try by creating below Measures

UOM  = var T1 = SUMMARIZE('Table','Table'[UOM],"Count",COUNTROWS('Table'))
       var T2 = MAXX(T1,[Count])
     return
       MAXX( T1, IF([Count] = T2,'Table'[UOM])) 
 
Latest Unit Price = var _M = MAXX('Table','Table'[Order Date])
                       return
                       CALCULATE(MAX('Table'[Unit Price]),'Table'[Order Date] = _M)
 
Most Repeated = var T1 = SUMMARIZE('Table','Table'[Unit Price],"Count",COUNTROWS('Table'))
            var T2 = MAXX(T1,[Count])
             return
MAXX( T1, IF([Count] = T2,'Table'[Unit Price]))
 

View solution in original post

1 REPLY 1
ddpl
Solution Sage
Solution Sage

@shareezsaleem try by creating below Measures

UOM  = var T1 = SUMMARIZE('Table','Table'[UOM],"Count",COUNTROWS('Table'))
       var T2 = MAXX(T1,[Count])
     return
       MAXX( T1, IF([Count] = T2,'Table'[UOM])) 
 
Latest Unit Price = var _M = MAXX('Table','Table'[Order Date])
                       return
                       CALCULATE(MAX('Table'[Unit Price]),'Table'[Order Date] = _M)
 
Most Repeated = var T1 = SUMMARIZE('Table','Table'[Unit Price],"Count",COUNTROWS('Table'))
            var T2 = MAXX(T1,[Count])
             return
MAXX( T1, IF([Count] = T2,'Table'[Unit Price]))
 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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