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!
Microsoft EmployeeIf 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.
I could do this using M code in the query editor.
Does anyone know how I could do this?
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!
- mahoneypat
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