Excel Forum

Count unique values in filtered power pivot table

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

December 15, 2021 - 12:02 pm

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

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 1844

Member Since:
November 8, 2013

Online

December 15, 2021 - 5:34 pm

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.

Shawn Wallack

Member

Members

Forum Posts: 77

Member Since:
December 7, 2021

Offline

December 15, 2021 - 10:43 pm

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:

The table (tblVelocity) and team name ([Team]) values within the FILTER function have been underlined in red by Power Pivot
Power Pivot also added additional parentheses to the end of the formula
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)

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