Forum Discussion
add multiple columns like countif
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.
My 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?
Hi, theo
Because addcolumn() function creates a table, you need to use the 'new table' feature.
PQ example count:
List.Count(Table.SelectRows(#"Changed Type",(x)=>x[name]=[name])[name])
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks v-janeyg-msft , however, when I try you solution for one column, it is not yet finished even after 10 minutes so I'm afraid it would be too long for the 1000 columns.
Whereas for this one, about 12 sec per column
COUNTROWS(FILTER('24Jun_1973',[average diff]=EARLIER([average diff])))Already tried the "new table" but same scalar error.
Appreciate any suggestion. thanks.
In relation to this, found some discussion on the use of the Tabular Editor. However, I am not well versed on the script creation. Would appreciate that can advise.
Hi, theo
In response to the information you gave, I can only respond like this. I also provided pq's solution, is there any problem?
Best Regards
Janey Guo
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.