Forum Discussion
Countif in DAX or Power Query
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.
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.
Thank you ,
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
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.
Thank you Cmcmahan, this is what i needed. I have no idea what you did here, but will have fund tring to learn :)
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.