Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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!  
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

ryan_mayu's avatar
ryan_mayu
Super User
7 years ago

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's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

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