Countif in DAX or Power Query

[filarap]

Hi all,

Please help as i was not able to find solution for what i require.
I have dataset with repeating ID number and i need to count how many times it repeats in a dataset. Groupby does not work, as i need data to remain ungrouped.

My goal is to count how many ids i have that apear more thatn one time in a table. Preferably i would like to do it in DAX, but if i can create this calculation in power query, i will be able to easily do it in DAX.

Below is example of dataset i have.

What I have	What I need
ID	Countif
1	3
2	2
1	3
3	1
4	1
5	1
6	1
1	3
2	2

Any help is greatly appreciated

Regards

Filarap

[sktneer]

How about this?

= CALCULATE(
	COUNT(Table1[ID]),
		FILTER(Table1,
			Table1[ID] = EARLIER(Table1[ID])
		)
	)

[filarap]

I tried using this, but for some reason, EARLIER does not recognise any column name.

[sktneer]

That's something strange.

This is the output returned by that formula....

[mussaenda]

you can use

CountIf =COUNT(Table1[ID])

[filarap]

Thank you mussaenda,

This will give me total count of ids, where i need to filter distinct ids that apear in list more than once.

From table provided, result should be 2 :

IDs 1 and 2 apear more thatn once in a dataset.

Hope this makes it clearer

Filarap

[mussaenda]

Power BI groups the ID though you choose do not summarize.

As a workaround, I used an index to prevent it from grouping then counted the ids.

[filarap]

Thank you mussaenda,

This is still not what i need. I suceeded to get same, by using id (Not sumarised) and count id without index.

Final result from table below should be 2, because we have 2 ids apearing more than once. Final result should be number representing number of unique ids with more thatn one occurance.

What would also help me, would be to have custom column in power query, showing count of occurances of id from row. Tried several options, but nothing works for me.

Thank you

Filarap

[Cmcmahan · Accepted solution]

If all you need is a measure for a card that displays the count of IDs that appear 2+ times, that should be pretty easy.  I'm not sure why you're so against using summarization or groupings for a single measure, but here's how I would do it:

CountMultiIDs = COUNTROWS(FILTER(ADDCOLUMNS( VALUES('Table'[ID]), "IDCount", CALCULATE(COUNTROWS('Table'))), [IDCount]>1))

The tricky bit here is to use CALCULATE around the inner COUNTROWS so that the DAX re-evaluates the context at which it is counting.

[filarap]

Thank you Cmcmahan, this is what i needed. I have no idea what you did here, but will have fund tring to learn 🙂

[Cmcmahan]

No problem. I'm always happy to break down my answers for beginners.

Here's the answer, but spaced out so it's easier to understand all the pieces. I've also color coded them so that I can refer to sections easier.

CountMultiIDs = 
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            VALUES('Table'[ID]), 
            "IDCount", CALCULATE(COUNTROWS('Table'))
        ), 
        [IDCount]>1
    )
)

Let's start at the innermost bit.  In black,  I'm using ADDCOLUMNS to take the list of all unique IDs (in red), and for each ID calculating the count of rows (in green).  This creates a virtual table in memory that looks like this.  Notice that it has already removed duplicate IDs, since we used VALUES instead of the ID column itself.

ID	IDCount
1	3
2	2
3	1
4	1
5	1
6	1

I wrapped that piece in a FILTER statement (in blue), with a filter condition that IDCount must be greater than 1.  That filters down the virtual table to this:

ID	IDCount
1	3
2	2

And finally, I used COUNTROWS (in pink) to count the rows in the filtered table.  Since the filtered table has the unique IDs in one columns and has filtered out rows where the IDCount is too low, counting those rows gives the final result you're looking for.

As always with DAX, there are multiple ways to accomplish this same result.  One alternate way would be to use SUMMARIZE instead of ADDCOLUMNS/VALUES syntax.  However, using SUMMARIZE to create calculated columns has been deprecated due to performance issues. You can read more about that here.

You can also replace VALUES with DISTINCT.  This has no appreciable difference on the outcome here, though VALUES can return a blank in certain situations.  You can also use COUNT('Table'[ID]) instead of the inner COUNTROWS('Table').  I'm not sure of any performance changes between the two, but I usually find it makes more logical sense to use COUNTROWS over COUNT unless you have a need to ignore nulls in a column.  

If you have any further questions, feel free to follow up.