Dax Help

[Anonymous]

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!

[ryan_mayu]

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

[Anonymous]

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!

[ryan_mayu]

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

[Anonymous]

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.

[v-juanli-msft · 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
    )
)

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]

@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

[Ashish_Mathur]

Hi,

Are you looking for a measure or a calculated column solution?  Share the link from where i can download your Excel workbook.

[itsmebvk]

@Anonymous please try the attched PBIX