Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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.
Hi @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.
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.
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.
Hi, @theo
If you can find a solution that meets your own conditions, I'm very happy. Can you kindly mark the helpful answer as a solution if you feel that makes sense?
Best Regards
Janey Guo
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.
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |