Countif for row based count (A:A=A2)

[Raindeer]

Hi All,

I have spend over 4 hours n trying to find answer for following but it looks like no one has asked this specific question so far.

I have 100k repeated values in column A and I have to get the number of times each value appears. To do this I use countif in Excel: =Countif(A:A=A2). How can I run same calculation in Power BI?

I found that following gave me exactly what I wanted: 

CALCULATE(COUNT(Table[Column]),ALL(Table),Table[Column]=EARLIER(Departures[Column]))

[Jenna619]

In your solution: 

CALCULATE(COUNT(Table[Column]),ALL(Table),Table[Column]=EARLIER(Departures[Column]))

Where does the Departures[Column] come from?

[Anonymous]

Try this

If your date only have a single column "Values", create a column and use the following Dax

Count_Same_Value = CALCULATE(COUNT('Table'[Values]))

If your date have more than one columns, use the following Dax

Count_Same_Value = CALCULATE(COUNT('Table'[Values]),ALLEXCEPT('Table','Table'[Values]))

[Raindeer]

Hi Jessica,

Do you think you can help me find a solution to this problem?

Kind regards

R

[MFelix]

Hi @Raindeer,

If you add the Values to a table and then add it again and select Count it will give you the expected result.

If you want  measure you need to do something like:

Count = COUNT ( TABLE[Column])

The measures are calculated based on the context so if you have a value as a row context it will make a filter of the count based on that row.

Regards,

MFelix

[Raindeer]

I just tried this solution but it only returns 1 for each value. I know there are supposed to be atleast 8 entries per value.  You will notice in the picture that some values appear multiple times.

Just to give you a bit of context, I am trying to calculate the number of times an activity takes place within one hour for a given date. Activity ID are unique and only appear once a day everyday. 

[MFelix]

Hi @Raindeer,

Are you making a Count or a Count distintc? 

Regards,

MFelix

[Raindeer]

I am counting the number of times same value appears entier column so I suppose its count.

[MFelix]

@Raindeer,

Check it on table visual at column level if you have the correct selection:

Regards,

MFelix

[Raindeer]

I need the count in column because I am going to use the values in a later calculation.

I just encountered a weird issue. You original suggestion works perfectly when wrapped in calculate.

Calculate(Count(Table[column]))

But it only works on strings when there are no dates in other columns. Have a look at following two screenshots. 

Second screenshot has same formula but different result. 

 

[MFelix]

Hi @Raindeer,

Try to do a COUNTA instead of COUNT without calculate, as in Excel Count is for numbers, COUNTA is for text.

Sorry for misleading you.

Regards

MFelix

[Raindeer]

Hi MFelix,

Thank you for your prompt reply.

I just tried CountA but its still not working. I am still seeing same result as my earlier screenshots. 

Do you think it could be becuase of some bug in Power BI?

Kind Regards

Raindeer

[MFelix]

Hi @Raindeer,

You should make a measure not a calculated column. The use of the measure will allow you to add context to your visuals and change the calculations only by adding or removing columns from your visuals.

Regards,

MFelix

[Raindeer]

Hi MFelix,

Thank you for your help. I will try measures and see if I can achieve my required objevtive.

[MFelix]

Hi @Raindeer,

Just to make this clear you just want to count how many times the A repeats in the full table right? You want this on a table visual.

Just add the ABC column on the table twice and change a summarization, if you need to have this calculated for using in a different way should use the measure.

Not really sure if this is what you want but this is a basic feature on PBI.

Or do you need to make any additional calculations or formulas.

Regards,

MFelix

[Raindeer]

Hi,

unfortunately my project is a little complex. The underlying goal is to weigh workload of individuals through counting the number of tasks they had at a given time. Each date and hour entry represent a task.  I normally do this through countif in Excel.

In attached picture, lookup is just date and hour with hyphen in middle. Values in column "table 1" is count of occurance lookup values appear in table 1 [=countif(Table1!C:C=C2)]. Same logic is applied on table 2 and 3. Workload is sum of each row.

I need to do exactly this in Power BI for a KPI but it looks like an impossible task 😞

[MFelix]

Hi @Raindeer,

You have to ways of doind this in PBI.

1 - Create a table with the Lookup values column (with unique values) and another one with date then just make a relationship between this two tables and your other 4 tables, thens just add the columns for each of your table and the total row will add up all the values.

2 - Make and append of all the tables in a single table in Query editor, one of the colums should be the table name, then just add the columns on the visual and the table name as column headers.

If you want I can prepare some sample PBIX file for you if you share the information that you have on the previous post has all your tables look like with the full details by line.

Regards,

MFelix