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.
Thank you
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 :)