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!
Discover more from BI Insight
Subscribe to get the latest posts sent to your email.
Hi there. I’ve read your post with interest… but there is a problem with this approach—the measures are not dynamic at all. That is, when you create a table with their values in one of the columns, the values are set in stone. Whatever conditions you’d like to slice by on the Power BI canvass, for instance, will not in the slightest impact the figures (and they should). This is probably not what you’d like to see. You’d like your measures to be quite the opposite and for this to work you cannot use the technique above. But there are ways to achieve fully dynamic measures that will react to other selections gracefully. Such a technique cannot, however, use calculated tables since these are calculated once and for all—when the model loads.
Hi Darek,
Welcome to BIInsight.com and thanks for your feedback.
Please note that in this method we are creating a calculated table, which means the table’s data gets updated only when you process the referenced table. In the described scenario we are creating a calculated table on top of “FactInternetSales”. So the calculated table’s data will be changed only when you process the referenced table which in our case is “FactInternetSales”.
For sure this is just one method of many other methods you can achieve similar or more complex scenarios.
Cheers.
This DAX Code is not working for me pls any other suggestion?
=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]
)
Hi Nonso,
Welcome to BIInsight.com.
The code you copied here works fine in my Test environment.
Please provide more info on why the code doesn’t work for you.
Do you get any errors?
Cheers.
Thanks Soheil for your quick responds.
I followed all your steps and was unsuccess in creating the table…the DAX Codes wouldn’t work on creating the calculated table, I don’t know if it had anything to do with my SSDT but i tried another computer still got same error message.
I am wondering if there can be any other way i can create the table?
Hi there,
What’s the error message though?
Having the error message would help me to have a better understanding of the issue.
You can also test the same scenario in Power BI.
Cheers.
Thanks Sohail, I’m able to create new calculated table and include measure in this new table but the the same measure value is showing against each dimension values.
Hi Swamy,
Welcome to BIInsight.com.
Please make sure you correctly create the calculated table in SSAS.
Follow the steps from this point onwards.
One more thing, this post is pretty old. Back then we had to be more creative in handling these sorts of requirements.
I encourage you to have a look at Calculation Groups. You can handle the method explained in this post much easier with Calculation Groups.
Good luck!
Unfortunatelly, not working for me neither. I followed step by step the instructions. The only error i get is “One or more formulas have errors”. What i am trying to achieve is to create a table with one column and only three values in it. Whatever i try it is just not working.
Hi Ivan.
What is your version of SSAS Tabular?
As mentioned in the Requirements section, you must have SSAS Tabular 2016 or later to use the methods explained in this post.
Hi, Not sure where to check the year but my version is 13.0.5102.14 Standard edition. Isn’t this 2016 release or i am wrong? Thanks
Hi Ivan,
The 13.x version is SQL Server 2016, but is it your SSAS version or your SQL Server DB Engine version?
What is the DAX expression you use to construct your table with one column having three values?
The expressions used in this blogpost are all working expressions, so please let me know what expression you use.
Thanks
2016 is the Engine i think. The way i check is I go to SSMS and connect to Analysis Services and from there i go to Report->Standard Reports.
What i am trying to to is to create simple table with 2 columns which later to connect to my model and to use it as filter. In column 1 values 1,2,3 and in column 2 values – value1,value2,value3. Thats all i need. I am trying to do it with SELECTCOLUMNS but it requieres existing table as first argument. Is there any other function i can use for this simple task?
Thanks
Hi Ivan,
You are right, to use the
SELECTCOLUMNS()
function you require to use a table on the function’s first argument.I clearly explained it in this post. But if it is not that clear to you I explain it again.
The following expression creates the table you are after using table constructors in DAX:
{
(1, "Value1")
, (2, "Value2")
, (3, "Value3")
}
Now you can use the above code as the first argument of your
SELECTCOLUMNS()
function as follows:SELECTCOLUMNS(
{
(1, "Value1")
, (2, "Value2")
, (3, "Value3")
}
, "Column 1", [Value1]
, "Column 2", [Value2]
)
Hopefully that helps.
I tried it already. Didn’t work. Whatever i try to create new table i always get “One or more formulas have an error”. Maybe something wrong with my analysis services. Anyway, Thanks for your time and replies.