Forum Discussion
add multiple columns like countif
Hi,
I have 2 questions and this may have been discussed elsewhere but I can't find it.
1. what is the dax required to count each entry - like a countif. see example below, target is Count_RC.
| ID | Rank code | Count_RC |
| 10000 | AAA | 2 |
| 10001 | AAA | 2 |
| 10002 | AAB | 7 |
| 10003 | AAC | 1 |
| 10004 | AAE | 1 |
| 10005 | AAV | 1 |
| 10006 | AAB | 7 |
| 10007 | AAB | 7 |
| 10008 | AAB | 7 |
| 10009 | AAB | 7 |
| 10010 | AAB | 7 |
| 10011 | AAB | 7 |
2. if I need to create multiple of these countifs, can I use the addcolumns to create multiple columns (around 1000 columns) rather than doing it one at a time?
target:
1 = ADDCOLUMNS('Filtering data',
countifDax1,
countifDax1
)
Thanks in advance.
Hi v-janeyg-msft , thanks for the solution but as mentioned PQ's solution takes a while to complete for my data, so I modified the column expression mentioned initially to a measure instead since I found this tabular editor that can create multiple measures all at once.
calculate(COUNTROWS(FILTER('28Jun_1973',[average diff]='28Jun_1973'[average diff])))Thanks for the help anyway.
10 Replies
Hey theo ,
in general you can do a kind of countif by just filtering.
For your first question as example:
Count_RC = VAR vRowRank = myTable[Rank] RETURN CALCULATE( COUNTROWS( myTable ), myTable[Rank] = vRowRank )Your second question I didn't really understand what you want to achieve. But you can add multiple criteria to your CALCULATE statement if that was the question.
If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicHi selimovd for the quick reply. Will try your advice and share the result.
the second part is that I need this count_RC to be created for more columns, as rankCode is just one of them.
since it is about 1000 columns, I am checking if there's a way that an addcolumns could add multiple countif columns .I dont want to add column 1000x.
Hey theo ,
with a "normal" table that's not possible. The only way I know is to unpivot your data, so every of the 1000 columns would become a row with "columnName" and the second one "columnValue". Then you can filter for the name and a specific value, but you would also have to put the criteria in the measure.
Where do you save the criteria? If it's in a table other approaches might work.
If you need any help please let me know.If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍Best regardsDenisBlog: WhatTheFact.biFollow me: twitter.com/DenSelimovicMy apology if not clear.
To clarify, my table has about 1200 columns of raw data. About 1000 columns need to have a calculated columns (COUNTS) which is the countif that I am looking for (see below example).
ID Rank code Average Diff size Count_RC Average Diff size 10000 AAA +01.00 to +01.25 2.5 2 5 5 10001 AAA +01.00 to +01.25 2.5 2 5 5 10002 AAB +01.00 to +01.25 2.5 1 5 5 10003 AAC +01.00 to +01.25 2.5 1 5 5 10004 AAE +01.00 to +01.25 2.5 1 5 5 here is my failed attempt:
addcolumns = ADDCOLUMNS('24Jun_1973', "count_average diff",COUNTROWS(FILTER('24Jun_1973',[average diff]=EARLIER([average diff]))), "count_rankCode",COUNTROWS(FILTER('24Jun_1973',[rankCode]=EARLIER([rankCode]))) )but always have this error; multiple columns cannot be converted to scalar value
I was able to create a countif DAX which is a bit faster than what you provided but thanks for the help.
COUNTROWS(FILTER('24Jun_1973',[average diff]=EARLIER([average diff])))Can it be done in power query instead?
