DAX: Using GROUPBY() to get a simple count of rows in groups

[Paradroid78]

Hi,

I have a simple table, let's say 2 columns ("a" and "b"), and I want to do the DAX equivalent of

`SELECT a, b, COUNT(*) from TABLE group by a, b`

So, I'm looking at GROUPBY and trying all sorts of things but can't work out how to do this:

`GROUPBY("table 1", [a], [b], "count", COUNTX(CURRENTGROUP())) // error: Must supply filter argument, but I don't want to filter`

`GROUPBY("table 1", [a], [b], "count", COUNTROWS(CURRENTGROUP()) // error: can't use CURRENTGROUP() in COUNTROWS()`

`GROUPBY("table 1", [a], [b], "count", COUNT(CURRENTGROUP()) // can't use CURRENTGROUP() in COUNT()`

It seems to me this should be the simplest thing ever, but I'm out of options. Could anybody help?

[v-danhe-msft]

Hi @Paradroid78,

Based on my test, you could refer to below steps:

Sample data:

Create a new table:

Table 2 = GROUPBY('Table1','Table1'[Qtr],"A",COUNTX(CURRENTGROUP(),'Table1'[Value]))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Paradroid78]

Thanks all, this helps a lot.

One thing I'm confused about, in the case of all answers using SUMMARIZE, the argument to the summary function seems need the name of the table the summary is being performed on.

What though if this is coming from a nested DAX computated table?

e.g. take Vic0810's answer:

SUMMARIZE('Table 1', a, b, "RowCount", COUNTROWS('Table 1'))

How would I structure the COUNTROWS call if that first argument was inlined, e.g.

SUMMARIZE(SELECTCOLUMNS(UNION('Table 1', 'Table 2'), "a", [a], "b", [b]), a, b, "RowCount", COUNTROWS(???))

As I haven't got a table name for the computed table, I'm not clear what to pass into the ??? above.

It's entirely possible that I may be fundamentally misunderstanding something about how that context sensitive argument to COUNTROWS works, in which case I apologize and hope someone can point me at something that will better my understanding...

[v-danhe-msft]

Hi @Paradroid78,

Could you have used my formula with GROUPBY function?

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[PattemManohar]

@Paradroid78 Please try using this, by adding "New Table" option

DAXOutput = SUMMARIZECOLUMNS(SimpleDAX[A],SimpleDAX[B],"Count",COUNTROWS(SimpleDAX)) 

[vik0810]

SUMMARIZE should do

SUMMARIZE('Table 1', a, b, "RowCount", COUNTROWS('Table 1'))