Forum Discussion
Count all values in table that share same value as current row in pivot table
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!
12 Replies
- nandukrishnavs
Community Champion
Anonymous
Did you check DISTINCTCOUNT() of Duplication key?
sjoerdvnSolution Sage
try:
=COUNTROWS(CALCULATETABLE(ALL('Invoice Data'),VALUES([Total Weight kg]), VALUES([Total Net Value])))
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
- sjoerdvn
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])))
- mahoneypat
Microsoft Employee
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)
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.
