Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
Hi @Anonymous
Create calculated columns
Column = CALCULATE ( SUM ( Sheet5[Daily Minutes Spent] ), FILTER ( ALLEXCEPT ( Sheet5, Sheet5[Name], Sheet5[Attendance Date], Sheet5[Attended] ), [Activity] = "Fitness" ) ) Column 2 = CALCULATE ( DISTINCTCOUNT ( Sheet5[Attendance Date] ), FILTER ( ALLEXCEPT ( Sheet5, Sheet5[Name] ), [Activity] = "Fitness" && [Column] >= 60 ) )
or create measures
Measure = IF ( MAX ( [Activity] ) = "Fitness", CALCULATE ( SUM ( Sheet5[Daily Minutes Spent] ), FILTER ( ALLEXCEPT ( Sheet5, Sheet5[Name], Sheet5[Attendance Date], Sheet5[Attended] ), [Activity] = "Fitness" ) ), " " ) Measure 2 = CALCULATE ( DISTINCTCOUNT ( Sheet5[Attendance Date] ), FILTER ( ALLEXCEPT ( Sheet5, Sheet5[Name] ), [Activity] = "Fitness" && [Measure] >= 60 ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous
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))
Proud to be a Super User!
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!
@Anonymous
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))
Proud to be a Super User!
For column 2, I need to calculate the number of uniques days that each participant has had at least 60mins of fitness. For AB the two days are be Oct. 31 and Nov. 16.
Hi @Anonymous
Create calculated columns
Column = CALCULATE ( SUM ( Sheet5[Daily Minutes Spent] ), FILTER ( ALLEXCEPT ( Sheet5, Sheet5[Name], Sheet5[Attendance Date], Sheet5[Attended] ), [Activity] = "Fitness" ) ) Column 2 = CALCULATE ( DISTINCTCOUNT ( Sheet5[Attendance Date] ), FILTER ( ALLEXCEPT ( Sheet5, Sheet5[Name] ), [Activity] = "Fitness" && [Column] >= 60 ) )
or create measures
Measure = IF ( MAX ( [Activity] ) = "Fitness", CALCULATE ( SUM ( Sheet5[Daily Minutes Spent] ), FILTER ( ALLEXCEPT ( Sheet5, Sheet5[Name], Sheet5[Attendance Date], Sheet5[Attended] ), [Activity] = "Fitness" ) ), " " ) Measure 2 = CALCULATE ( DISTINCTCOUNT ( Sheet5[Attendance Date] ), FILTER ( ALLEXCEPT ( Sheet5, Sheet5[Name] ), [Activity] = "Fitness" && [Measure] >= 60 ) )
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-juanli-msft thank you! Can you assist with the formula for post below?
https://community.powerbi.com/t5/Desktop/DAX-formula-help/m-p/604541#M287776
Hi,
Are you looking for a measure or a calculated column solution? Share the link from where i can download your Excel workbook.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |