Query on (CountA with All function)

[DJ]

Hi All,

I was wrote following DAX formula to count distinct values present in a column. "=CountA(All(EmpTable[EmpName]))" but it gives error

Then I tried "=Countrows(All(EmpTable[EmpName]))" and it worked.

Can you make me understand why first formula didn't work? "ALL" function provided distinct values and "CountA" should have just counted resulted column. In Fact CountRows is also performing same calculation.

Thanks,
DJ

 

[GraH - Guido]

Hi, why not simply use DISTINCTCOUNT()?
ALL() does not provide a distinct list of values/tables, it gives all the rows of a table or all the values in a column. The result is in fact a table.
From DAX help:
The 'COUNTA' function only accepts a column reference as an argument.
Countrows can handle a table as input. As it counts the rows of a table, regardless of the number of columns present.

 

[DJ]

Thanks GraH for the suggestion and explanation. It helped me understanding the logic.

 

[NARAYANK991]

Hi ,

I think that the reason to use the ALL keyword is to ensure that any filters or other selections which might be present on the table , are ignored when passing the result to the COUNTROWS function or any other function which might be used outside.

I think Chihiro can post more details.

Narayan

 

[GraH - Guido]

Yes indeed NARAYANK991. ALL is a filter function, in this case it removes all filters and also returns ALL rows.
From https://msdn.microsoft.com:
Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

But, you already knew that. Just added it as reference for DJ.

 

[GraH - Guido]

Thanks GraH for the suggestion and explanation. It helped me understanding the logic.

you're welcome.

 

[Chihiro]

Yep, GraH nailed it. ALL is useful function when you want to calculate % of Total in Measure. Since, it will ignore all filter context.

 

[DJ]

Need one more help (again with counting)

I have a list of repeated values in a column. Want to show number of times a values is repeating in next column. For example:

Column-1 Column-2
A 3
A 3
A 3
B 2
B 2
C 4
C 4
C 4
C 4
D 1

Please suggest a formula for this.

DJ

 

[Chihiro]

Typically you'd create another table with distinct values of the Column1.

And then you'd use relationship between the column. Calculate count on the distinct table. Then lookup value back into original.

First step is shown in link below.
https://stackoverflow.com/questions...in-one-table-based-on-values-in-another-table

Though I prefer to do this sort of set up in PowerQuery stage.

 

[GraH - Guido]

Suppose it is Table1, the formula you'd like is perhaps this one
=COUNTAX(filter(Table1,Table1[Column1]=earlier(Table1[Column1])),Table1[Column1])
But, I follow Chihiro's suggestion to have the counter only once in a distinct value table and use that in measures where needed.