One of our customers had read Nick Barclay’s post on dynamic sets in SSAS 2008 and was hoping that this would mean that you could create KPI’s with dynamic dimension members. Well the answer is yes and no. It’s already possible to do this in Monitoring (or should I say PerformancePoint Services) using custom sets in the scorecard designer (more on this below). However in PPSM these sets are resolved at the point the Scorecard is rendered in the browser.
This is fine as long as the members of your sets are not affected by the filters applied to your scorecard (member.children for example) – unfortunately the set does not get re-queried when you change a filter. For instance if you were to create a set of your top 10 customers and drag that onto the rows of your scorecard, changing a time filter will not cause the KPI dimension members to change even if you’ve used time.currentmember in your set definition. So you may end up displaying the Top 10 customers for the current month which may be different to the Top 10 for the selected time period.
Update: Please see Nick Barclay’s comment below for a very neat solution to this issue using filter link formulas. (Wish I’d thought of that!)
Custom sets in the scorecard designer aren’t the most obvious thing to use nor are they very user-friendly. Your best bet is to use a tool like SQL Management Studio/Mosha MDX Studio to design a query that you know works then paste out the MDX for the set into the custom set formula editor. You access this by dragging the Custom item in the Details pane onto the relevant position on your scorecard:
Paste your set query in to the pop-up dialog. For Example:
TOPCOUNT(
[Product].[Product Model Categories].[Subcategory].members,
10,
[Measures].[Internet Sales Amount]
)
You can then use the update button on the edit tab of the ribbon to see the results. Unfortunately there’s no way to edit the custom set once you’ve added it. You have to delete the dimension members and then add a new custom set.
Pareto Charts in Power BI and the DAX behind them
The Pareto principle, commonly referred to as the 80/20 rule, is a concept of prioritisation.
Apr
Databricks: Cluster Configuration
Databricks, a cloud-based platform for data engineering, offers several tools that can be used to
Apr
AI Assistance in Microsoft Fabric
The exponential growth of Large Language Models (LLMs) couples with Microsoft’s close partnership with OpenAI
Apr
10 reasons why it’s worth the effort to understand the value of your data
“If leaders really want to create a data driven culture, the journey starts with them!
Apr
Content Safety in Azure AI Studio
Azure AI Content Safety is a solution designed to identify harmful content, whether generated by
Apr
Model Benchmarks in Azure AI Studio
In the constantly changing field of artificial intelligence (AI) and machine learning (ML), choosing the
Apr
Celebrating International Women’s Day: from Classroom to Code
As we celebrate International Women’s Day, I want to share my journey of breaking stereotypes
Mar
Pretty Power BI – Adding Pagination to Bar Charts
Good User Experience (UX) design is crucial in enabling stakeholders to maximise the insights that
Feb