I was recently approached by a client where we have assisted in implementing a Microsoft Analysis Services tabular solution and a supporting management pack of SSRS reports. They were asking about utilising some of the KPI’s defined in their tabular cube in the SSRS reports using DAX queries. I was at a bit of a loss and most web help seemed to suggest it couldn’t be done:
However, with a bit of time to investigate it appears Power View does now support Tabular KPI’s and as such a DAX query should be able to pull them out.
My KPI base measure is named “Highest Ranking” and I am able to view its value, status or goal in Power View. A little work with every SQL developer’s best friend Profiler and I had what I was looking for. It appears that when you create a KPI SSAS is defining some measures in the background which do some of the work of the MDX KPI functions.
Therefore the following DAX query against my cycling Demo cube contains the expected results and could therefore be used in SSRS reporting.
EVALUATE ADDCOLUMNS(
VALUES(‘DimRider'[Name])
,
“Highest_Ranking”, ‘FactRanking'[Highest Ranking],
“Highest_Ranking_Goal”, ‘FactRanking'[_Highest Ranking Goal],
“Highest_Ranking_Status”, ‘FactRanking'[_Highest Ranking Status]
)
ORDER BY ‘DimRider'[Name]
I also tested for the existence of other KPI functions such as Trend and Weight but these do not appear to be present. It is also interesting that the use of a KPI over a measure does not change the measure name but just group it in the field list and as such there is no need for a value function.
For more info on KPI’s in Power View there is additional documentation here. I am currently unsure of the required versions for this functionality so if it is missing from your environment I would love to hear from you.
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr