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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Corleen
Regular Visitor

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

13 REPLIES 13
Phil_Seamark
Employee
Employee

Hi @Corleen

 

This worked for me (I think)

 

# Times = CALCULATE(
				COUNTROWS('Table1'), 
				FILTER(
					'Table1',
					'Table1'[SYSTEM_NUM] = MAX('Table1'[SYSTEM_NUM])
					)
				)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

@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

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

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

@Corleen,

 

Please try the DAX below.
# times =
CALCULATE(
                    COUNTROWS('TransDetail'),
                    FILTER(ALLEXCEPT('TransDetail',                                                                                        
                                                  'TransDetail'[SYSTEM_NUM]), 

                               'TransDetail'[EARN_DEDUCT_IND]=1)
                    )

 

Regards,

Charlie Liao

Anonymous
Not applicable

Hello,

 

I am facing the same problem with getting count of measure. Can you please provide a relevent solution.

 

Thanks,

rax

Anonymous
Not applicable

I am facing the same issue with getting count of measure output. Can you please help in providing the specific solution!

 

Thanks

remove =1

I have removed the 1 and it gives me a result.

But not the result what I want.

 

Would please explain the Measure to me

 

Thank you

This measure simply count the number of rows in SYSTEM_NUM that matches each row in EARN_DEDUCT_IND no matter what is the value in EARN_DEDUCT_IND

Amratya
Helper I
Helper I

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],
        )
)

 

 

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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