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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AlexEnergee3
New Member

DAX Calculate the Max for a Group with SUM

Hello,

the follow query

 

evaluate 
SUMMARIZECOLUMNS(
    Machines[BillOfResources],
    Machines[Name],
    FeedbackDetails[Code],
    FeedbackDetails[description],
    FeedbackDetails[DescriptionLocal],
    --"Number", CALCULATE(DISTINCTCOUNT(Feedbacks201801[FeedbackID]),Filter(Dates,Dates[TimeStamp]>=Date(2018,02,02)),Filter(Dates,Dates[TimeStamp]<=Date(2018,02,28)),Filter(Machines,Machines[BillOfResources]="Mold1")),
    "TotalPeriod", CALCULATE(Sum(Feedbacks201801[Period]),Filter(Dates,Dates[TimeStamp]>=Date(2018,02,02)),Filter(Dates,Dates[TimeStamp]<=Date(2018,02,28)),Filter(Machines,Machines[BillOfResources]="Mold1"))
)
order by [totalperiod] desc

 

return the follow result

 

BillOfResourcesNameCodeDescriptionDescriptionLocalTotalPeriod
Mold1Mold1.DryEntry52Alarm description 52Alarm local description 5221
Mold1Mold1.GreenCut31Alarm description 31Alarm local description 3120
Mold1Mold1.ConveyorsExitDryer114Alarm description 114Alarm local description 11416
Mold1Mold1.ConveyorsExitDryer1Alarm description 1Alarm local description 115
Mold1Mold1.Dry28Alarm description 28Alarm local description 2815
Mold1Mold1.CutExit18Alarm description 18Alarm local description 1815
Mold1Mold1.Lamgea125Alarm description 125Alarm local description 12515
Mold1Mold1.CutExit118Alarm description 118Alarm local description 11815
Mold1Mold1.Lamgea60Alarm description 60Alarm local description 6015
Mold1Mold1.CutExit15Alarm description 15Alarm local description 1515
Mold1Mold1.ConveyorsExitDryer55Alarm description 55Alarm local description 5515
Mold1Mold1.CutExit66Alarm description 66Alarm local description 6615
Mold1Mold1.Dry8Alarm description 8Alarm local description 815
Mold1Mold1.DryEntry19Alarm description 19Alarm local description 1915
Mold1Mold1.Lamgea77Alarm description 77Alarm local description 7714

 

I need to obtain the Max Total Period column grouped by Name column

 

Can anyone help me?

 

Thanks!

1 REPLY 1
v-xjiin-msft
Solution Sage
Solution Sage

Hi @AlexEnergee3,

 

In your scenario, you can using SUMMARIZECOLUMNS() function to create a new calculated table. Right?

 

Then to achieve your requirement, you can create a new measure in this new calculated table. The expression is like:

 

MAX Total Period =
CALCULATE ( MAX ( Table[TotalPeriod] ), ALLEXCEPT ( Table, Table[Name] ) )

Thanks,
Xi Jin.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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