topic Count all values in table that share same value as current row in pivot table in DAX Commands and Tips

Count all values in table that share same value as current row in pivot table

Anonymous — Wed, 29 Apr 2020 09:23:52 GMT

I have a duplication key and I want to see how many time this key shows up in the rest of the table.

Duplication key:

=CONCATENATE(CONCATENATE([Total Weight kg],"-"),[Total Net Value])

Key Count:

=COUNTAX(ALL('Invoice Data'),CONCATENATE(CONCATENATE([Total Weight kg],"-"),[Total Net Value])=[Duplication Check Key])

But at the moment I am getting this:

410.556-0	11912
3702.224-0	11912
869.4-0	11912
408.29-0	11912
1356.356-0	11912
361.496-0	11912
1773.014-0	11912
1240.89-0	11912

If I do the same thing without the ALL function:

=COUNTAX('Invoice Data',CONCATENATE(CONCATENATE([Total Weight kg],"-"),[Total Net Value])=[Duplication Check Key])

I only count items in the current row and I count all of them.

The difficulty is that you cannot simply use "@"[column] like you can in an excel table. Please someone help

Re: Count all values in table that share same value as current row in pivot table

nandukrishnavs — Wed, 29 Apr 2020 11:43:44 GMT

Anonymous

Did you check DISTINCTCOUNT() of Duplication key?

Re: Count all values in table that share same value as current row in pivot table

sjoerdvn — Wed, 29 Apr 2020 12:14:07 GMT

try:

=COUNTROWS(CALCULATETABLE(ALL('Invoice Data'),VALUES([Total Weight kg]), VALUES([Total Net Value])))

Re: Count all values in table that share same value as current row in pivot table

Anonymous — Wed, 29 Apr 2020 13:06:58 GMT

Again.... Why are you not doing this in Power Query, where such calculations belong?

Best
D

Re: Count all values in table that share same value as current row in pivot table

Anonymous — Wed, 29 Apr 2020 13:41:23 GMT

sjoerdvn Unfortunately this does not work. It seems to be iterating weight and net value so it counts everything

Anonymous I am trying both in Power Pivot and in the data model in Power Query

Re: Count all values in table that share same value as current row in pivot table

sjoerdvn — Wed, 29 Apr 2020 14:35:19 GMT

So, is [Duplication Check Key] a measure or a computed column ?

What are the required output columns of your report ?

Anyway, I've done a little test. Assuming the [Key] column is in the required output, you could use:

= COUNTROWS(CALCULATETABLE('Invoice Data',ALL('Invoice Data'),VALUES('Invoice Data'[Total Net Value]),VALUES('Invoice Data'[Total Weight kg])))

Re: Count all values in table that share same value as current row in pivot table

mahoneypat — Thu, 30 Apr 2020 00:51:54 GMT

If Duplication Key is an existing calculated column, why not just make a table visual with Duplication Key and the following measure?

RowCount = countrows('Invoice Data')

If the count of rows with same Duplication Key is needed in a calculated column, you could use the following:

RowCount = var currentDupKey = [Duplication Key]

return calculate(countrows('Invoice Data'), all('Invoice Data'), 'Invoice Data'[Duplication Key] = currentDupKey)

Re: Count all values in table that share same value as current row in pivot table

Anonymous — Thu, 30 Apr 2020 09:27:11 GMT

sjoerdvn this still does not work, it reads all of the rows as before.

mahoneypat how does the currentDupKey part work? That is what I am trying to achieve.

Re: Count all values in table that share same value as current row in pivot table

Anonymous — Thu, 30 Apr 2020 09:31:12 GMT

I could do this using M code in the query editor.

Does anyone know how I could do this?

Re: Count all values in table that share same value as current row in pivot table

Anonymous — Thu, 30 Apr 2020 11:34:48 GMT

have found a solution to this.

Step 1)

Go into main table query, remove any errors from the net value column (optional)

Create key from relevant columns

Close and load (to data model)

Step 2)

Duplicate this query

Use group by to get a row count using the key as the group category.

Close and load (to data model)

Build a relationship with the original table

Step 3) Use related to get the count column in order to use a 'downstream' variable in any Power Pivot Table (optional)

Thanks for your support peeps!

Re: Count all values in table that share same value as current row in pivot table

mahoneypat — Thu, 30 Apr 2020 11:57:59 GMT

Glad you figured it out. You could also do a merge in query editor and bring your count into the first table there, and then diable load on that query so you don't have that extra table in your model.

Regards,

Pat

Re: Count all values in table that share same value as current row in pivot table

Anonymous — Thu, 30 Apr 2020 13:05:02 GMT

Great thanks for the tip!

Re: Count all values in table that share same value as current row in pivot table

Anonymous — Thu, 30 Apr 2020 13:06:39 GMT

Thats great advice, thanks Pat