Forum Discussion

Corleen's avatar
Corleen
Regular Visitor
9 years ago

Count a Measure or Countif

Good day   Please assist   I've created a measure to count how many times a person id comes forth in the data.   # times = CALCULATE(COUNT('TransactionDetails'[SYSTEM_NUM]),'TransactionDetails'...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi Corleen

 

This worked for me (I think)

 

# Times = CALCULATE(
				COUNTROWS('Table1'), 
				FILTER(
					'Table1',
					'Table1'[SYSTEM_NUM] = MAX('Table1'[SYSTEM_NUM])
					)
				)
Corleen's avatar
Corleen
Regular Visitor
9 years ago

Phil_Seamark

 

Thank you for your reply.

But it is a measure not a calculated column 

 

For eg.

 

Trans Once = count([# times] = 1) I want the answer for this, how to do this?

Trans Twice = count([# times] = 2) ect.

where 

[# times] = CALCULATE(COUNT('TransDetail'[SYSTEM_NUM]),'TransDetail'[EARN_DEDUCT_IND]=1)  (this is a measure)

 

Thanks

  • Amratya's avatar
    Amratya
    Advocate I
    9 years ago

    Can you try the same formula for the measure and check what it results?

    • Corleen's avatar
      Corleen
      Regular Visitor
      9 years ago

      # times =

      CALCULATE(

                         COUNTROWS('TransDetail'),

                                                ALLEXCEPT('TransDetail',

                                                                                       'TransDetail'[SYSTEM_NUM],

                                                                                         'TransDetail'[EARN_DEDUCT_IND]=1) 'the underline part gives an error'

      )t

       

      ! A single value for column EARN_DEDUCT_IND  in TransDetail cannot be determined. THis can happen when a measure formula to a column that contains many values without specifying an aggregation such as min, max, count or sum to get to a single result.

       

      Is this what you meant? 

      • Amratya's avatar
        Amratya
        Advocate I
        9 years ago

        remove =1