Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

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       04/02/2018           1

BB       04/03/2019           1

BB       04/04/2019           0

CC      04/01/2019           1

CC      04/02/2019           0

CC      04/02/2019           1

 

I need a measure which will count the distinct dates in COLB within COLA where COLC = 1 and the year(COLB) = YEAR(MAX(COLB)).

 

Using the data above, the results should be  3. 

COLA   COLB                COLC

AA       04/03/2019          1

BB       04/01/2019          1

CC      04/02/2019           1

 

Thanks.

1 ACCEPTED SOLUTION

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:

Annotation 2020-04-22 180339.png

 

I have modified the pbix file,pls click here.

 

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

View solution in original post

7 REPLIES 7
v-kelly-msft
Community Support
Community Support

Hi @Anonymous ,

 

You need a measure as below:

 

MesureValue = 
var t = ALLSELECTED('Table')
return
SUMX (
    'Table',
    VAR a = [COLA]
    RETURN
        IF (
            'Table'[COLC] = 1
                && NOT (
                    [COLB]
                        IN CALCULATETABLE (
                            DISTINCT ( 'Table'[COLB] ),
                            FILTER (
                                t,
                                var a2 = 'Table'[COLA]
                                var maxyear = YEAR (
                                            CALCULATE (
                                                MAX ( 'Table'[COLB] ),
                                                FILTER (t, 'Table'[COLA] = a2 )
                                            )
                                        )
                                return
                                'Table'[COLA] < a
                                    && 'Table'[COLC] = 1
                                    && YEAR ( 'Table'[COLB] )
                                        = maxyear
                            )
                        )
                )
                && YEAR ( [COLB] )
                    = YEAR (
                        CALCULATE (
                            MAX ( 'Table'[COLB] ),
                            FILTER ( t, 'Table'[COLA] = a )
                        )
                    ),
            [COLC],
            BLANK ()
        )
)

 

And you will see:

Annotation 2020-04-17 123756.png

For the related .pbix file,pls click here.

 

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

Kelly,

 

Thank you for your response, I agree the solution worked with the sample data I provided, however, when I substituted live data, the solution did not work.   Also, maybe I did not explain my requirements correctly.  The table on the left is the raw data.  I need to count the total number of "people days" for the oldest year in the table.    The table contains a record for each task completed on a given day.  The measure should only count the person once on any day.

 

So, for example, DW completed 3 task on 3/2/18, CJ completed 2, and DM completed 2.  The total count for 3/2/18 should be 3 since only DW, CJ, and DM reported task completed on that day.

 

The table in the center is the date and Measure you provided and the card below shows the results of the measure. 

 

Overall, the measure calculation is close, at lease it only counting 2019 days.

 

PBI Q4.jpg
I attempted to tweak the calculation, but got myself into trouble.  If you could review the calculation, I would appreciate it.

 

Thanks,

Rick

Anonymous
Not applicable

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

Anonymous
Not applicable

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

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:

 

Annotation 2020-04-21 122233.png

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
Not applicable

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

PBI Q6.jpg

Sorry for the confusion.

 

 

 

 

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:

Annotation 2020-04-22 180339.png

 

I have modified the pbix file,pls click here.

 

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.