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 ,

 

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:

For the related .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

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.

 


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

 

Thanks,

Rick

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