This all sounds great, but there’s a big barrier when it comes to KPIs: workers really dislike tracking them, and the biggest culprit is having to manually enter the KPIs values. Because we experienced this first hand, our team made KPI automation a cornerstone feature of PowerBI Scorecards.

You can import data in PowerBI Scorecards using 3 methods:

• • Manual KPI input
  • Importing KPI values through an Excel file
  • Importing KPI values automatically:
    • using a SQL Database connection
    • using a Microsoft Analysis Service connection

Since the first two are covered in PowerBI Scorecards’ 101, we’ll focus on importing values automatically.

From the PowerBI Scorecards web agent, go to Settings and click on Connections.

Click the Create button on the top right corner and a modal will open.

We will now cover the configuration of SQL and DAX connections.

To configure a SQL Connection, you must name it and choose SQL as its type. Then, under Value, you must enter the connection string where the values will be fetched. When you’re done, press create.

Using the SQL Connection is pretty straightforward as you only need to choose this type of connection in the configuration of the KPI and input the formula in the fields as in the example shown below:

• • In the green box: set the KPI to automatic.
  • In the red box: choose the type of SQL connection string.
  • In the blue box: input the formula or query that to be executed to get this KPI’s value.

The automatic KPI configuration using a DAX Connection is exactly the same, but you’ll need a template that returns the values exactly by this order: Area/SubArea/KPIValueNumerator/KPIDenomitanator(if applicable)/KPINumeratorYTD(if applicable)/KPIDenominatorYTD(if applicable).

Once again, click Settings from the menu and choose Connections. Name your connection and choose DAX as its type. Paste your connection string under Value and the template that returns your values under Template.

Below is an example of a DAX template:

Please note that templates need to be set according to your own business logic.

DEFINE
VAR yearFilter =
FILTER ( KEEPFILTERS ( VALUES ( Calendar[Year] ) ), Calendar[Year] = /*YearDateParam*/ )
VAR monthFilter =
FILTER ( KEEPFILTERS ( VALUES ( Calendar[Month (#)] ) ), Calendar[Month (#)] = /*MonthDateParam*/ )
evaluate
SELECTCOLUMNS (
SUMMARIZECOLUMNS (
'Calendar'[Month (#)],
'Calendar'[Year],
'Stock Item'[Category],
yearFilter,
monthFilter,
"Numerator", /*NumeratorFormulaParam*/,
"Denominator", /*DenominatorFormulaParam*/,
"NumeratorYTD", /*NumeratorYTDFormulaParam*/,
"DenominatorYTD", /*DenominatorYTDFormulaParam*/
),
"Data", DATE ( [Year], [Month (#)], 1 ),
"Area", [Category],
"SubArea", "ALL",
"Numerator", [Numerator],
"Denominator", [Denominator],
"NumeratorYTD", [NumeratorYTD],
"DenominatorYTD", [DenominatorYTD]
)


The parameters below need to be on the template as well since they will be replaced with the formula defined in the KPI Configuration:

/*YearDateParam*/
/*MonthDateParam*/
/*NumeratorFormulaParam*/
/*DenominatorFormulaParam*
/*NumeratorYTDFormulaParam*
/*DenominatorYTDFormulaParam*/

Thank you for making it this far. We hope this guide was comprehensive and compelling enough to make your organization configure its own automatic KPIs. If you still need help doing so, our team is always happy to help at support@devscope.net.