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!
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
Microsoft Employee
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
Great thanks for the tip!
