Compartilhar via

KPI Goal format not working in SQL Server Analysis Service Tabular project

  • Artigo

While working on customer issue recently, came across following scenario where there was a problem in the format of goal of KPI in tabular model. When we set the format for the measure and use it as target measure for KPI, the Goal value doesn’t show up in the format of the measure when we browse the goal from client tools like Excel or PowerBI. If we browse the measure directly, that shows up in the correct format. We do not see this behavior in Multidimensional cube. The format for goal works fine in Multidimensional cube.

Steps to reproduce the issue:

  • Create the table with two columns. One for Value measure and one for Goal measure

  • Create a tabular model based on this table
  • Create 2 measures based on the 2 columns and change their format to Percentage

ValueMeasure:=SUM(TestTable[ValueSource])

GoalMeasure:=SUM(TestTable[GoalSource])

  • Create a KPI based on the Value measure with the Goal measure as the target measure

  • Deploy the solution to a tabular model analysis server 2016
  • Connect with client tool (example: Excel) to the model and select Value and Goal from the ValueMeasure KPI.

  • Now you see that Value is formatted as percentage, but ValueMeasure Goal is not formatted as percentage

By following the points below we observed that we are able to get the goal in percentage format:

  • Format of the Goal as well as the measure should be set before we create the KPI
  • The compatibility level of the project has to be set to 2016 versions (that is 1200 or above)

  • Now it shows the correct format for the goal

 

Author:     Chaitra Hegde – Support Engineer, SQL Server BI Developer team, Microsoft

Reviewer:   Sarath Babu Chidipothu  – Support Escalation Engineer, SQL Server BI Developer team, Microsoft