The second important point to understand about the VALUES function is that you can’t put a table into a cell without performing some sort of aggregation on it first, since a table can potentially contain multiple values. The VALUES function in this case returns a single-column table which looks like this for each of the 5 countries: In the measure above, the COUNTROWS function expects to receive a table: The first is that it returns a table of data. The above example shows two important features of the VALUES function. The Number of cities column shows the number of cities for each country, using the following measure:ĭisplaying this measure in our report would give:įor the total row there are lots of cities in the current filter context, so naturally you get the More than one city message. Note for this example that I’ve used a filter (not shown here) on the report to avoid showing any blank countries). Suppose you create this table in a Power BI report: To explain what this sentence means, here’s an example. The VALUES function returns the table of data for the current filter context.
#Ssas tabular distinct count how to
The rest of this article shows what this function does, and how to use it to create a range of effects in your Power BI reports. Learning the CALCULATE function is key to understanding how to create measures in DAX, but the VALUES function runs it a close second. The process of changing row context into filter context in this way is called context transition. For each country, this creates a filter context, limiting the rows in the sales table to those for the country in question, and hence giving a different answer for each row of the above table. The CALCULATE function doesn’t just allow you to change the filter context, it can create it, too.
Total sales B = CALCULATE ( SUMX ( Sales, * ) ) The figure for Timmy Tortoise for London is 75%, which is: Now suppose that you change your pivot table to show the number of sales as a percentage of the total for each column. These are the three sales which took place for this product in this country and city. If you were to double-click on this cell in Excel, you would see the underlying rows: The filter context for the shaded cell containing the number 3 is therefore as follows: The figure selected shows that there were three sales of Timmy Tortoise products in London (UK): Suppose you have the following pivot table in Excel, showing the number of sales for each country, city, and product in the database. Filter Context Explained Using an Excel Pivot Table To understand the CALCULATE function, you must understand filter context, so that’s where I’ll begin for this article. You can import this data into your own Power BI data model by first downloading this Excel workbook, or by running this SQL script in SQL Server Management Studio.Īs for the previous articles in this series, everything I describe below will work just as well in Power BI, PowerPivot or Analysis Services (Tabular Model), each of which Wise Owl train. This database shows sales of three toys for different cities around the world:
This article uses the same simple database as its two predecessors. A delegate on one of my courses adopted the policy of starting every formula with =CALCULATE, and it’s not such a bad approach! This article explains how to use the CALCULATE function and also how to use the (almost) equally important VALUES function. The book will tell you that the CALCULATE function is at the heart of everything that you do in DAX and is the key to understanding the language. If you should ever start reading a book on DAX, you will quickly reach a chapter on the CALCULATE function. Creating Time-Intelligence Functions in DAX.Cracking DAX – the EARLIER and RANKX Functions.Using the DAX Calculate and Values Functions.
0 kommentar(er)
