Dynamic Named Sets with Power BI
In this article, we will explain about the use of dynamic named sets in SSAS and how along with SCOPE statement it can be used to address tricky challenges. Let’s assume that our requirement is to show a measure (say Last 3 year’s sales amount) as a KPI in power BI dashboard which is connected to SSAS MOLAP cube and the dashboard has a slicer selection of from and to year.
Below code is used to calculate Last 3 Year Sales
CREATE MEMBER CURRENTCUBE.[Measures].[Last 3 Year Sales]
AS
SUM(PARALLELPERIOD([Order Date].[Calendar Hierarchy].[Year],2,[Order Date].[Calendar Hierarchy].CURRENTMEMBER):([Order Date].[Calendar Hierarchy].CURRENTMEMBER),[Measures].[Sales Amount]),
FORMAT_STRING = "Currency",
LANGUAGE = 2057,
VISIBLE = 1, ASSOCIATED_MEASURE_GROUP = 'Fact Internet Sales';
When we look at the above expression, CURRENTMEMBER determines which year value is being passed.
Now let us pass the year values ‘from’ and ‘to’ date from Power BI report to see how the last 3-year sales behave with the above code.
In the above screenshot, we are passing ‘from’ date as 2010 and ‘to’ date as 2013. If we look at the Last 3 Year Sales measure the value is showing the total of Sales amount for all the years which is wrong. Ideally, 2013 being the maximum year selected, the last 3-year measure should only include the sales amount for the years 2011, 2012 and 2013 (i.e. the value should be £29,269,561.46)
This problem can be easily addressed with the help of dynamic named sets.
First we create a dynamic named sets consisting of years.
CREATE DYNAMIC SET CURRENTCUBE.[Selected Years]
AS [Order Date].[Calendar Hierarchy].[Year].MEMBERS;
Then we modify the Last 3 Year Sales calculated measure as below
CREATE MEMBER CURRENTCUBE.[Measures].[Last 3 Year Sales]
AS
SUM(PARALLELPERIOD([Order Date].[Calendar Hierarchy].[Year],2,TAIL([Selected Years]).Item(0)):
(TAIL([Selected Years]).Item(0)),[Measures].[Sales Amount]),
FORMAT_STRING = "Currency",
LANGUAGE = 2057,
VISIBLE = 1, ASSOCIATED_MEASURE_GROUP = 'Fact Internet Sales';
Upon refresh, when we look at the below screenshot we are able to see the correct values for Last 3 year sales and dynamic named sets have solved the issue.
We can also extend the above solution using scope statements where we can place the year column/attribute across rows as well as along the slicers.
Now let us see what happens when we place the year attribute on rows.
From the above screenshot we see that the once again Last 3 year Sales is showing wrong values. It’s displaying the sales amount for the last selected item in the slicers for other years as well.
This can be resolved by SCOPE statements.
We will first have to create a hidden measure, which uses the dynamic named sets.
CREATE MEMBER CURRENTCUBE.[Measures].[Last 3 Year Sales Hidden]
AS
SUM(PARALLELPERIOD([Order Date].[Calendar Hierarchy].[Year],2,TAIL([Selected Years]).Item(0)):
(TAIL([Selected Years]).Item(0)),[Measures].[Sales Amount]),
FORMAT_STRING = "Currency",
LANGUAGE = 2057,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Internet Sales';
Then we will create a new measure assigning NULL value in the beginning and wrap this measure using scope statement.
CREATE MEMBER CURRENTCUBE.[Measures].[Last 3 Year Sales]
AS NULL,
FORMAT_STRING = "Currency",
LANGUAGE = 2057,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact Internet Sales';
SCOPE([Order Date].[Calendar Hierarchy].MEMBERS,[Measures].[Last 3 Year Sales]);
THIS = CASE
WHEN
[Order Date].[Calendar Hierarchy].CURRENTMEMBER.LEVEL IS [Order Date].[Calendar Hierarchy].[Year]
THEN
SUM(PARALLELPERIOD([Order Date].[Calendar Hierarchy].[Year],2,[Order Date].[Calendar Hierarchy].CURRENTMEMBER):([Order Date].[Calendar Hierarchy].CURRENTMEMBER),[Measures].[Sales Amount])
ELSE
[Measures].[Last 3 Year Sales Hidden]
END;
END SCOPE;
Now when we refresh the power BI report, we will be able to see the correct values once again as shown in the screenshot below
After removing the year from rows
Incorporating dynamic named sets in SSAS solution can resolve many complex requirements.