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'[EARN_DEDUCT_IND]=1)

 

What I would like to do is now is count how many people transacted once, how many people transacted twice and so on. Also make a interval for example transacted 15-25 times. In Excel it would be something like this countif(A2:A500,1)

 

I've tried the if statement in Power BI  if(# times = 1, count([# times])) but you can't count that measure.

 

Any ideas or better way to approach this will be appreciated 

 

Thank you

Need Help

13 Replies

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

  • Amratya's avatar
    Amratya
    Advocate I
    9 years ago

    Hi Corleen,

     

    Usually you can use below in counting reference to a column

     

    CALCULATE (
        COUNTROWS ( 'TableName' ),
        ALLEXCEPT (
            'TableName',
            'TableName'[Coulmn_you_want_to_count],
            'TableName'[Reference_Coulmn],
            )
    )

     

     

    • Corleen's avatar
      Corleen
      Regular Visitor
      9 years ago

      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)

      where 

      [# times] = CALCULATE(COUNT('TransDetail'[SYSTEM_NUM]),'TransDetail'[EARN_DEDUCT_IND]=1)

       

      Thanks

      • Amratya's avatar
        Amratya
        Advocate I
        9 years ago

        I think if you gave this a try you will have all you need in one column

         

        #times = CALCULATE(COUNTROWS('TransDetail'),ALLEXCEPT('TransDetail','TransDetail'[SYSTEM_NUM],'TableName'[EARN_DEDUCT_IND],))

         

        It will count all trnx that have EARN_DEDUCT_IND = 1, 2, 3, ...etc and place it in one column