This is the starting time weblog postal service dedicated to Analysis Services on the Power BI web log. Every bit discussed in this post on the old Analysis Services squad blog, Microsoft is moving off MSDN and TechNet blogs. Nosotros have fabricated information technology articulate that Power BI will be a one-stop shop for both enterprise and self-service BI on a single, all-inclusive platform. Power BI will be a superset of Assay Services, so there is no better place to announce hereafter Assay Services features than here on the Power BI blog. The Analysis Services engine is at the foundation of Power BI and powers its datasets, so this just feels correct.

The adding groups characteristic was announced for SQL Server Assay Services 2019 in the CTP 2.three weblog post. CTP 3.0 provides enhancements on acme of calculation groups. Calculation groups volition soon be officially launched and supported in Azure Analysis Services and Power BI Premium through the XMLA endpoint .

CTP 3.0

We are excited to announce the public CTP 3.0 of SQL Server 2019 Analysis Services. This public preview includes the following enhancements for Analysis Services tabular models.

· Dynamic format strings with adding groups

· Calculation group back up for MDX queries

Dynamic format strings

Until now, tabular models in Analysis Services and Ability BI have supported dynamic formatting of measures using the FORMAT function. The FORMAT function has the disadvantage of returning a cord, forcing measures that would otherwise be numeric to be returned as strings. This causes limitations such as non working with nearly Power BI visuals depending on numeric values like charts, etc.

Dynamic format strings with adding groups allow conditional awarding of format strings to measures without forcing them to render strings.

Dynamic format string instance for time intelligence

Delight refer to the time-intelligence example provided in the CTP two.three blog post . All the calculation items except YOY% should use the format of the electric current measure out in context. Sales YTD should exist currency; Orders YTD should be a whole number. YOY% nevertheless should be percent regardless of the format of the base measure.

For YOY%, we tin can override the format string by setting the format string expression holding to "0.00%;-0.00%;0.00%". For more than data on the sections of format strings in Analysis Services, please encounter this document .

The following matrix visual in Power BI shows that Sales Electric current/YOY and Orders Current/YOY retain their corresponding base measure format strings. Sales YOY% and Orders YOY% however override the format string to apply percentage format.

Dynamic Format String Time Intelligence

Dynamic format string example for currency conversion

Currency conversion takes dynamic format strings to the next level. Consider the post-obit Run a risk Works information model. It is modeled for "ane-to-many" currency conversion as divers past this document .

One to Many Currency Conversion

The FormatString cavalcade is added to the DimCurrency table and populated with format strings for the respective currencies.

Format String Column

This download file contains sample format strings.

The post-obit calculation grouping is divers.

Table Currency Conversion
Cavalcade Conversion Calculation
Precedence 5
Calculation Item 
                  "No Conversion"
Expression 
                  SELECTEDMEASURE()
Calculation Item 
                  "Converted Currency"
Expression 
                  IF(                  //Check one currency in context & non US Dollar, which is the pin currency:                  SELECTEDVALUE( DimCurrency[CurrencyName],                  "US Dollar"                  ) =                  "United states of america Dollar",                  SELECTEDMEASURE(),                  SUMX(                  VALUES(DimDate[Engagement]),                  Calculate(                  Separate(                  SELECTEDMEASURE(),                  MAX(FactCurrencyRate[EndOfDayRate]) ) )     ) )
Format Cord Expression 
                  SELECTEDVALUE(     DimCurrency[FormatString],                  SELECTEDMEASUREFORMATSTRING() )

The format cord expression must return a scalar string. It uses the new SELECTEDMEASUREFORMATSTRING() function to revert to the base of operations mensurate format string if in that location are multiple currencies in filter context.

The post-obit visual shows the dynamic format of the Sales measure.

Currency Conversion

Previously, using the FORMAT function, it was not possible to switch visual blazon to a chart and brandish the values. Using dynamic format strings with calculation groups, we tin.

MDX query support for calculation groups

In previous CTPs, adding groups were not enabled for MDX queries. Now they are, and so clients such every bit Excel tin can do good.

Tooling

Calculation groups are currently engine-just features. SSDT support will come before SQL Server 2019 general availability. In the meantime, you can utilize the fantastic open-source customs tool Tabular Editor to create calculation groups. Alternatively, you lot can employ SSAS programming and scripting interfaces such as TOM and TMSL .

New 1470 compatibility level

To use calculation groups, existing models must be upgraded to the 1470 compatibility level. 1470 models cannot be deployed to SQL Server 2017 or earlier or downgraded to lower compatibility levels.

Download now

To get started with SQL Server 2019 CTP 3.0, find download instructions on the SQL Server 2019 web page . Enjoy!