Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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.

View solution in original post

8 REPLIES 8
ryan_mayu
Super User
Super User

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

c1.JPG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

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

c1.JPG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

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.

Anonymous
Not applicable

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
itsmebvk
Continued Contributor
Continued Contributor

@Anonymous please try the attched PBIX

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.