Share via


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.