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

 

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

    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!

     

    • itsmebvk's avatar
      itsmebvk
      Continued Contributor
      7 years ago

      Anonymous please try the attched PBIX

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