Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Help needed for Measure

Assuming data as follows:   COLA   COLB                COLC AA       04/01/2018          1 AA       04/02/2018          0 AA       04/03/2019          1 BB       04/01/2019           1 BB     ...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi Anonymous ，

     

    Just modify measure as below:

     

    Measure = 
CALCULATE(DISTINCTCOUNT('Table'[COLA]),FILTER(ALLEXCEPT('Table','Table'[COLB]),YEAR(SELECTEDVALUE('Table'[COLB]))=MAXX(ALL('Table'),YEAR('Table'[COLB]))&&'Table'[COLC]=1))+0

     

    And you will see:

     

    I have modified the pbix file,pls click here.

     

     
    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Sorry,  The total should be 19.    9 count on 3/6/19 and 10 count on 3/7/19.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

v-kelly-msft . Please ignore my previous comment.  The caffien had not kicked in when I posted it.  The correct values are as stated previously.

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

    Go to query editor>add column>Index column;

    Then create 3 measures as below:

     

    Measure = 
CALCULATE(COUNTROWS('Table'),FILTER(ALLEXCEPT('Table','Table'[COLB]),YEAR(SELECTEDVALUE('Table'[COLB]))=MAXX(ALL('Table'),YEAR('Table'[COLB]))&&'Table'[COLC]=1))+0
    Measure 2 = 
var c = CALCULATE(COUNTROWS('Table'),FILTER(ALL('Table'),'Table'[COLB]=MAX('Table'[COLB]) && 'Table'[Index]<=MAX('Table'[Index])),VALUES('Table'[COLB]))
Return
IF(c>1,BLANK(),1)
    _total = 
SUMX(FILTER('Table','Table'[Measure]>=1&&'Table'[Measure 2]=1),'Table'[Measure])

     

    Finally you will see:

     

    For the related .pbix file,pls click here.

    Hope this is what you need.

     

     

    Best Regards,
    Kelly
    Did I answer your question? Mark my post as a solution!
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      v-kelly-msft   Hello Kelly,

       

      I'm sorry, the result is not correct.  I think the result of "_total" sums COLC.  The value I need is the number of COLA per distinct COLB where COLC = 1 within the year(max(COLB).  COLB is the task completion date.  An employee can have multiple tasks completed on a given date.

       

      For example, if CJ has 2 completions (COLC = 1) , DM has 2 completions, and SS has 1 completion on 3/6/19 the number of "people days" on 3/6/19 would be 3.  If CJ has 2 completions on 3/7/19 and SS has 2 completions on 3/7/19, the number of "people days" on 3/7/19 would be 2.   In this case, the number of "people days" for 2019 would be 5.

       

      I can then take the total number of completions (sum of COLC) and divide by the "people days" for 2019 and get an average of (9/5) = 4.5 task per day.

       

      My test data is attached.

       

      				
COLA, COLB, COLC, Index
AA, 4/1/2018, 1, 0
AA, 4/2/2018, 0, 1
AA, 4/3/2019, 1, 2
BB, 4/1/2019, 1, 3
BB, 4/2/2018, 1, 4
BB, 4/3/2019, 1, 5
BB, 4/4/2019, 0, 6
CC, 4/1/2019, 1, 7
CC, 4/2/2019, 0, 8
CC, 4/2/2019, 1, 9
DW, 3/2/2018, 1, 10
DW, 3/2/2018, 1, 11
DW, 3/2/2018, 1, 12
CJ, 3/2/2018, 1, 13
CJ, 3/2/2018, 1, 14
DM, 3/2/2018, 1, 15
DM, 3/2/2018, 1, 16
KB, 3/5/2018, 1, 17
DB, 3/5/2018, 1, 18
DW, 3/5/2018, 1, 19
DW, 3/5/2018, 1, 20
DW, 3/5/2018, 1, 21
CJ, 3/5/2018, 1, 22
CJ, 3/5/2018, 1, 23
KB, 3/6/2019, 1, 24
KB, 3/6/2019, 1, 25
DW, 3/6/2019, 1, 26
DW, 3/6/2019, 1, 27
CJ, 3/6/2019, 1, 28
CJ, 3/6/2019, 1, 29
DM, 3/6/2019, 1, 30
DM, 3/6/2019, 1, 31
SS, 3/6/2019, 1, 32
KB, 3/7/2019, 1, 33
KB, 3/7/2019, 1, 34
DB, 3/7/2019, 1, 35
CC, 3/7/2019, 1, 36
DW, 3/7/2019, 1, 37
EH, 3/7/2019, 1, 38
CJ, 3/7/2019, 1, 39
CJ, 3/7/2019, 1, 40
DM, 3/7/2019, 1, 41
DM, 3/7/2019, 1, 42

       

      Here is the results of the 3 measures.

      Sorry for the confusion.

       

       

       

       

      • v-kelly-msft's avatar
        v-kelly-msft
        Community Support
        6 years ago

        Hi Anonymous ，

         

        Just modify measure as below:

         

        Measure = 
CALCULATE(DISTINCTCOUNT('Table'[COLA]),FILTER(ALLEXCEPT('Table','Table'[COLB]),YEAR(SELECTEDVALUE('Table'[COLB]))=MAXX(ALL('Table'),YEAR('Table'[COLB]))&&'Table'[COLC]=1))+0

         

        And you will see:

         

        I have modified the pbix file,pls click here.

         

         
        Best Regards,
        Kelly
        Did I answer your question? Mark my post as a solution!