Reply
St1 Frequent Visitor
Frequent Visitor
Posts: 2
Registered: Friday

Dax Help

Hi folks - I need help with the formulas to produce the results in the last 2 (green highlighted) columns below. Any help would be greatly appreciated!

 

daxhelp.PNG

Member
Posts: 155
Registered: ‎09-17-2018

Re: Dax Help

@St1

 

You can try this solution.  Thanks

 

column = SUMX(FILTER(Sheet2,Sheet2[time]=EARLIER(Sheet2[time])),Sheet2[mins])
Column 2 = CALCULATE(COUNT(Sheet2[name]),FILTER(Sheet2,Sheet2[Activity.1]="Fitness"&&Sheet2[mins]=60))

c1.JPG

 

St1 Frequent Visitor
Frequent Visitor
Posts: 2
Registered: Friday

Re: Dax Help

Syntax not getting to the correct numbers in the final 2 columns (green highlighted) - I've added some nuance to the sample dataset below to show exactly what I'm working with. Again any help is greatly appreciated!

 

daxhelp1.PNG

Regular Visitor
Posts: 24
Registered: ‎12-27-2018

Re: Dax Help

@St1 please try the attched PBIX

Highlighted
Member
Posts: 155
Registered: ‎09-17-2018

Re: Dax Help

@St1

 

Could you please explain the logic of two green columns with more detailed ifno?

 

I am guessing the logic for column 1 is sum the minutes when the same person's attend date is also the same. What's more, only calculate when activity is Fitness.

 

The logci for column 2 is count the record numbers if the acitivity is fitness and minutes is 60. However, for Amy Brown. She has three records fit that condition. What's the logic to get 2 in the result?

Thanks

 

Column = if('Sheet1'[Activity]="Fitness",SUMX(FILTER(Sheet1,Sheet1[Name]=EARLIER(Sheet1[Name])&&'Sheet1'[DATE]=EARLIER('Sheet1'[DATE])),Sheet1[MINS]))
Column 2 = CALCULATE(COUNT(Sheet1[Name]),FILTER(ALLEXCEPT(Sheet1,Sheet1[Name]),Sheet1[Activity]="Fitness"&&Sheet1[MINS]=60))

c1.JPG