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