cancel
Showing results for
Did you mean:
St1 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
ryan_mayu 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))` St1 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! itsmebvamsi Regular Visitor

## Re: Dax Help

@St1 please try the attched PBIX

Highlighted
ryan_mayu 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))` St1 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,

Are you looking for a measure or a calculated column solution?  Share the link from where i can download your Excel workbook. 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.

St1 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