cancel
Showing results for
Did you mean:
Frequent Visitor

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

Re: Dax Help

Hi @St1

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.

8 REPLIES 8
Established Member

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))`

Frequent Visitor

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!

Regular Visitor

Re: Dax Help

@St1 please try the attched PBIX

Highlighted
Established Member

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))`

Frequent Visitor

Re: Dax Help

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.

Super User

Re: Dax Help

Hi,

Community Support Team

Re: Dax Help

Hi @St1

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.

Frequent Visitor

Re: Dax Help

@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