cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
St1 Frequent Visitor
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!

 

daxhelp.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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
    )
)

10.png

 

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

c1.JPG

 

St1 Frequent Visitor
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!

 

daxhelp1.PNG

itsmebvamsi Regular Visitor
Regular Visitor

Re: Dax Help

@St1 please try the attched PBIX

Highlighted
ryan_mayu Established Member
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))

c1.JPG

 

St1 Frequent Visitor
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
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
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
    )
)

10.png

 

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