• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Count unique values in filtered power pivot table|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Count unique values in filtered power pivot table|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryCount unique values in filtered pow…
sp_PrintTopic sp_TopicIcon
Count unique values in filtered power pivot table
Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
1
December 15, 2021 - 12:02 pm
sp_Permalink sp_Print

I have a Power Pivot table called "tblVelocity"...

  • There is a column called "Team" (and there are many teams in the underlying Excel table)
  • Each row of the Power Pivot table represents team performance during the previous scrum sprint as measured in a column called "Ratio". It looks like this:
    • ROW 1 = [Team] = "Yankees"; [Sprint] = "Sprint-1"; [Ratio] = 95%
    • ROW 2 = [Team] = "Mets"; [Sprint] = "Sprint-1"; [Ratio] = 105%
    • ROW 3 = [Team] = "Giants"; [Sprint] = "Sprint-1"; [Ratio] = 100%
    • ROW 4 = [Team] = "Yankees"; [Sprint] = "Sprint-2"; [Ratio] = 87%
    • ROW 5 = [Team] = "Mets"; [Sprint] = "Sprint-2"; [Ratio] = 102%
    • ROW 6 = [Team] = "Giants"; [Sprint] = "Sprint-2"; [Ratio] = 96%

As you can see, there are 3 teams, each with 2 [Sprint] and [Ratio] values. I use this Power Pivot table to inform a pivot table in Excel. I then use that pivot table to inform a standard (non-pivot) chart in Excel. The chart visualizes team performance over time, and it works as expected.

What I am struggling to do is find a way to modify the value of "ratio" depending on the unique team count after a filter is applied in the Excel pivot table. For example, imagine I click the [Team] slicer on the Excel table and multi-select "Yankees" and "Mets". What I want to do is force all the [Ratio] values to zero so that the chart effectively does not render anything meaningful. I only want to render the chart if the filter returns exactly one team.

What I think I need to do is create a formula-based "Adjusted Ratio" field in the Power Pivot table and set its value to:

  • IF (Visible_Unique_Team_Count = 1, [Ratio], 0 * [Ratio])

Then I can base my chart on the [Adjusted Ratio] (instead of [Ratio]), and when it's equal to 0 the chart won't have any data to show.

The problem is that I can't figure out how to calculate "visible unique team count." I have tried various versions of this:

Visible_Unique_Team_Count := 
SUMX ( VALUES ( tblVelocity[Team]),
CALCULATE ( DISTINCTCOUNT ( tblVelocity[Team] ), tblVelocity[Team]=[@[Team]]))

I have seen only one of two results. Either the formula I use results in an error or it results in the total count of teams regardless of whether the filter is applied (and so the ratio is never adjusted).

Thanks

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
2
December 15, 2021 - 5:34 pm
sp_Permalink sp_Print

Hi Shawn,

You cannot use dynamic team name to filter the CALCULATE function:

CALCULATE ( DISTINCTCOUNT ( tblVelocity[Team] ), tblVelocity[Team]=[@[Team]]))

CALCULATE accepts values like:

CALCULATE ( DISTINCTCOUNT ( tblVelocity[Team] ), tblVelocity[Team]="Team Name"))

For dynamic values, you have to use the FILTER function:

CALCULATE ( DISTINCTCOUNT ( tblVelocity[Team] ), FILTER(tblVelocity[Team], tblVelocity[Team]=[@[Team]]) )

If you want it to react based on pivot slicer context, use ALLSELECTED() function in CALCULATE filter.

Avatar
Shawn Wallack
Member
Members
Level 0
Forum Posts: 77
Member Since:
December 7, 2021
sp_UserOfflineSmall Offline
3
December 15, 2021 - 10:43 pm
sp_Permalink sp_Print sp_EditHistory

Excel-Error-1.JPGImage Enlarger

Thank you, Catalin. I appreciate your quick and thorough response. Unfortunately, that is one of the ways I previously attempted to do this. However, the following formula causes the error below.

Please notice:

  1. The table (tblVelocity) and team name ([Team]) values within the FILTER function have been underlined in red by Power Pivot
  2. Power Pivot also added additional parentheses to the end of the formula
  3. The actual name of the field I use is "Team Count Helper", not "Visible_Unique_Team_Count" 

Team Count Helper := CALCULATE ( DISTINCTCOUNT ( tblVelocity[Team] ), FILTER(tblVelocity[Team], tblVelocity[Team]=[@[Team]]) )

============================
Error Message:
============================

The end of the input was reached.
The calculated column 'tblVelocity[Team Count Helper]' contains a syntax error. Provide a valid formula.

============================
Call Stack:
============================

at Microsoft.AnalysisServices.AnalysisServicesClient.SendExecuteAndReadResponse(ImpactDetailCollection impacts, Boolean expectEmptyResults, Boolean throwIfError)
at Microsoft.AnalysisServices.AnalysisServicesClient.Alter(IMajorObject obj, ObjectExpansion expansion, ImpactDetailCollection impact, Boolean allowCreate, XmlaWarningCollection warnings)
at Microsoft.AnalysisServices.Server.Update(IMajorObject obj, UpdateOptions options, UpdateMode mode, XmlaWarningCollection warnings, ImpactDetailCollection impactResult)
at Microsoft.AnalysisServices.Server.SendUpdate(IMajorObject obj, UpdateOptions options, UpdateMode mode, XmlaWarningCollection warnings, ImpactDetailCollection impactResult)
at Microsoft.AnalysisServices.MajorObject.Update(UpdateOptions options)
at Microsoft.AnalysisServices.BackEnd.MajorObjectUpdateEntryCollection.DoUpdate()
at Microsoft.AnalysisServices.BackEnd.MajorObjectUpdateOptimizer.CommitUpdates()
at Microsoft.AnalysisServices.BackEnd.SandboxTransactionAmo.CommitUpdates()
at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.CommitUpdates()
at Microsoft.AnalysisServices.BackEnd.DataModelingView.Update()
at Microsoft.AnalysisServices.BackEnd.DataModelingView.UpdateAddedColumns(List`1 addedColumns, List`1 oldExpressions, IList`1 colIndices)
at Microsoft.AnalysisServices.BackEnd.DataModelingView.ChangeFormulas(IList`1 colIndices, IList`1 names, IList`1 expressions, Boolean suspendImpact)
at Microsoft.AnalysisServices.BackEnd.DataModelingView.ChangeFormulas(IList`1 colIndices, IList`1 names, IList`1 expressions)
at Microsoft.AnalysisServices.Common.SandboxEditor.ChangeFormula(TableWidgetPanel currentTable, IList`1 colIndices, IList`1 names, IList`1 expressions, Boolean doFormulaBarCommit, IList`1 displayIndices)

============================

sp_PlupAttachments Attachments
  • sp_PlupImage Excel-Error-1.JPG (90 KB)
Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1807
Member Since:
November 8, 2013
sp_UserOfflineSmall Offline
4
December 15, 2021 - 11:24 pm
sp_Permalink sp_Print

In Power Query/Power Pivot, there is no row context, calculations are made on entire columns.

Therefore, @ cannot be used as you do in excel tables to indicate current row. Just refer to column names in formulas added to calculated columns.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ruth Savage, Andy Kirby, Roy Lutke, Jeff Krueger, Tom VAN LOO, Nada Perovic
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31889
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.