A while ago I was working on a Power BI project which the customer wanted to define a new table directly in the model. The easiest way to achieve this in Power BI Desktop is to “Enter Data” which creates a new table by typing or pasting in new contents. I was thinking of that project the other day and thought, hey, how we can do the same in SSAS Tabular when there is no Power Query (M) language available in SSAS Tabular 2016. The good news is that Power Query will be available in the next version of SSAS Tabular in SQL Server vNext. But, until then a workaround would be entering data to a CSV file then load it to the model. Another way is to implement custom tables in DAX in SQL Server 2016 Tabular models using Table and Row Constructors. In this post I show you a way of creating custom table in SSAS Tabular using table constructors in DAX. You can do the same in Power BI as the same principle applies. Therefore, in case you’d prefer not to use “Enter Data” feature which effectively uses Power Query to create a new table in Power BI Desktop, then you can use DAX to do the same.

Requirements

• SQL Server 2016 You can download SQL Server 2016 Developer Edition for free.
• SQL Server Analysis Services Tabular 2016 (SSAS Tabular)
• SQL Server Data Tools for Visual Studio 2015 (SSDT 2015)
• AdventureWorksDW
• SQL Server Management Studio 2016 (SSMS)

If don’t already have SQL Server 2016 it’s probably time to download and install it. I use AdventureWorksDW as sample database in this article.

Scenario

You are involved with an SSAS Tabular project and the customer asked for a report in Power BI with dynamic Card so that the values shown in the Card visual should dynamically change based on selected measure from a slicer. You have several different measures in the model and the customer wants to show some of them dynamically in only one Card visual. Consider you have the following measures to be shown in the Card:

• Total Internet Sales
• Internet Sales in 2014
• Total Number of Internet Sales Transactions

You have to create a logic so that the users can selected any of the above measures to show in a single Card visual.

How it works

After you meet the requirements, you’re good to start implementing the above scenario in SQL Server Data Tool (SSDT). Creating a calculated table in SSAS Tabular 2016 is fairly easy. All we need to do is to create a custom table with two columns. One column stores friendly names for measures and the other one holds DAX expressions for the measures. As you might have noticed, I’m talking about creating a custom table in DAX and populating it with values. Continue reading to see how. What we are going to do is to create a calculated table using table constructors in DAX. Table and Row Constructors weren’t available in previous versions of DAX in SSAS Tabular. They are very similar to Lists or a list of Tuples just like what we have in MDX.

I’ll explain this later when we created our sample model in SSDT.

Create a New SSAS Tabular Project in SSDT

As I stated before SSAS Tabular 2016 is needed to implement the following method. The reason is that we have to create a calculated table in our model and calculated table is NOT available in prior versions of SSAS Tabular.

• Open SSDT
• Create a new SSAS Tabular project

• Click “Integrated workspace” and click OK

• Click “Model” menu then click “Import from Data Source”
• Enter SQL Server name then select AdventureWorksDW and click Next

• Enter “Impersonation Information” then click Next

• Stick to the default then click Next
• Select “FactInternetSales” and “DimDate” then click Finish

So far we created an SSAS Tabular project in SSDT 2015 and loaded FactInternetSales to the model.

Create Needed Measures
In this scenario we need to create 3 new measures as follows:

Total Internet Sales := SUM ( 'FactInternetSales'[SalesAmount] )

Internet Sales In 2014 := CALCULATE(SUM( 'FactInternetSales'[SalesAmount] ), 'DimDate'[CalendarYear] = 2014)

Total Number of Internet Sales Transactions := COUNTROWS ( 'FactInternetSales' )

Create a Custom Table Using Table and Row Constructors DAX

Creating a Calculated Table in SSDT is relatively easy. We just need to click the corresponding button and create a new table using table and row constructors. Let me explain Table and Row Constructors a little bit.

Table Constructor in DAX

Table constructor is a new feature in DAX which is really cool. To construct a table, we need to put the values in curly brackets.  For instance, to create a table of “Blue”, Orange”, “Red” we just need to write the following:

{“Blue”, “Orange”, “Red”}

To see how it works in DAX:

• In SSDT, right click the model from “Solution Explorer” and click “Properties”

• Copy workspace server from SSDT

• Open SQL Server Management Studio 2016 (SSMS)
• Connect to the local instance of SSAS Tabular by pasting the workspace server Address you copied from SSDT

• Open a new MDX query and run the following DAX query

EVALUATE
    {"RED", "BLUE","ORANGE"}

Yes, you ran a DAX query in an MDX query editor as no DAX query editor currently available in SSMS.

The result shows a table of Blue, Orange and Red.

Row Constructor in DAX

To construct a row, we can put the values in parenthesis and then encapsulate the whole in curly brackets like {(“Blue”, Orange”, “Red”)}. Run the following DAX query in SSMS to see the results:

EVALUATE
    {("Blue", "Orange", "Red")}

Combining Table and Row Constructors in DAX

Now that you got the idea how to construct a table  or a row, it’s easy to combine those two together. Run the following DAX query in SSMS.

EVALUATE
    {("Banana", "Yellow"), ("Cucumber", "Green")}

As you can see, there are column names which added automatically. But, they are not really useful. So let’s put some meaningful names on the columns. We can easily use SELECTCOLUMNS() function in DAX to achieve this. Run the following DAX query and see the results:

EVALUATE
    SELECTCOLUMNS(
       {("Banana", "Yellow"), ("Cucumber", "Green")}
       , "Fruit Name", [Value1]
       , "Colour", [Value2]
       )

Brilliant.

Now let’s implement our scenario using the above technique. What we need to do is to create a custom table containing measure names and the actual measures themselves. Copy, paste and run the following DAX query in SSMS:

It is interesting isn’t it? What the above query does is that it runs the explicit measures we defined earlier and put the results in a table.

The next step is to rename the columns using SELECTCOLUMNS(). The query below does the job:

Let’s get back to our solution in SSDT and create a calculated table using the above technique.

Creating Calculated Table in SSAS Tabular

• Switch back to the SSAS tabular solution in SSDT and click to create a calculated table

• Copy and paste the following DAX expression

=SELECTCOLUMNS(
             {("Total Internet Sales", [Total Internet Sales])
             , ("Internet Sales In 2014", [Internet Sales In 2014])
             , ("Total Number of Internet Sales Transactions", [Total Number of Internet Sales Transactions])}
             , "Measure Name", [Value1]
             , "Measure Results", [Value2]
             )

• You can rename the calculated table by double clicking in the table tab

So far we successfully created a calculated table containing our measure names and their values.

Remember: We supposed to show dynamic measures in a single Card visual in the out visualisation. The presentation layer can be Power BI, Excel etc.

To be able to achieve the goal of showing the measures dynamically we need to create a new measure which basically shows the values of a selected measure by the end user.

• Create a new measure and copy/paste the following DAX expression:

Dynamic Measure:= CALCULATE(VALUES('Dynamic Card'[Measure Results])
            , filter(ALLSELECTED('Dynamic Card'[Measure Name])
                , 'Dynamic Card'[Measure Name]=[Measure Name]))

You’ll get the following error immediately after you create the new measure:

“MdxScript(Model) (1, 59) Calculation error in measure ‘Dynamic Card'[Dynamic Measure]: A table of multiple values was supplied where a single value was expected.”

The reason we get the above error message is that we are passing multiple values to the measure rather than a single value in the filter part. We can put a constant value in the filter, but, it is not what we want. Looking at the DAX expression you’ll quickly see that we’ve used ALLSELECTED() to filter the values based on whatever the end user selects in the report. So we need to put a Slicer on the report later. So don’t worry about the error message. But, bear in mind that the user should select only one value from the Slicer, so we’ll need to setup the Slicer as single select.

Let’s browse our SSAS Tabular model in Excel.

Browsing SSAS Tabular in Excel

You can easily browse the model in Excel to test the functionality we are looking for.

• Click on the Excel button on SSDT to open Excel and browse the model

Note: You’ll need to have Microsoft Excel installed on your machine to be able to browse your model in Excel.

• Click OK on the “Analyse in Excel” window
• Click “Dynamic Measure”
• You’ll immediately get an error message

• Right click “Measure Name” from fields list and click “Add as Slicer”

• Now select an item in the slicer

And this is what you get… Success!

Connect to SSAS Tabular from Power BI

• Open Power BI Desktop
• Get data from SQL Server Analysis Services
• Use workspace server from SSDT as server name (you learnt how to do that earlier in this article. In short, you can find it in the model properties)
• Live connect to the model
• Put a car visual on the report and tick “Dynamic Measures”
• You’ll get an error message, you know that
• Add a Slicer to the report then select “Measure Name”
• Select an Item from the Slicer

Formatting the Dynamic Measures

So far we achieved the goal, but, as you can see the result is not that informative and useful. When you select different items from the slicer the value shown in the Card visual is not that informative by itself. So let’s add some formatting to the “Dynamic Measure”. To do so, we need to modify the DAX expression we used to construct our calculated table.

• Switch back to SSDT
• Click “Dynamic Card” tab and modify the DAX expression as below

=  SELECTCOLUMNS(             {("Total Internet Sales", FORMAT([Total Internet Sales], "Currency"))           , ("Internet Sales In 2014", FORMAT([Internet Sales In 2014], "Currency"))              , ("Total Number of Internet Sales Transactions", FORMAT([Total Number of Internet Sales Transactions] ,"0,0"))}            , "Measure Name", [Value1]              , "Measure Results", [Value2]           )

• Switch back to Power BI Desktop and refresh data

• That looks nice, but, if you have OCD like me then you’d prefer to use the following DAX expression which adds some text to the measure to make it self explanatory

• Refresh data again in Power BI Desktop and here is what you get
• Set “Word wrap” to “On” for Card visual

The job is done!

This would my last post in 2016, so I wish you all a wonderful and happy Christmas.

See you next year!