KPIs #2: Developing KPIs in Power BI and Analysis Services using Tabular Editor

This is a follow up to my post exploring KPIs within Analysis Services. You may want to read that post first as it provides an introduction to KPIs.

I received some feedback from Daniel Otykier (author of Tabular Editor) regarding my last post and it inspired me to dive deeper into how KPIs are handled within Analysis Services, how you can create them using Tabular Editor, as well as their implementation within Power BI.

Without wasting any time, below are my observations after a further look into KPIs.

Observation 1: KPIs create “hidden” measures that are able to be referenced in other DAX expressions.

Daniel is completely correct in his reply to my tweet above, you can reference the automatically generated hidden measures related to your KPI in other parts of your model as if they were regular measures. They are in the form of [_MeasureName Goal], [_MeasureName Status] and [_MeasureName Trend], just as Daniel described.

Interestingly, DAX Studio currently doesn’t organize the [_MeasureName Trend] measure in the same way as the others with their shortform names (e.g. “Value”, “Goal”, and “Status”).

DAX Studio KPI Trend Query
DAX Studio querying KPI trend and status

Note that in SSDT the “hidden” KPI measures will appear with a red squiggly line underneath them, indicating that SSDT does not recognize the measures, however, they should still evaluate correctly.

Observation 2: StatusExpression and TrendExpression can both be created in Tabular Editor and they should evaluate to a number corresponding with their StatusGraphic and TrendGraphic.

Currently when developing KPIs in SSDT the StatusExpression is created automatically when you use the KPI dialog. Note, you are not able to create a TrendExpression through the SSDT KPI dialog and there are no hints that the property even exists.

On the other hand, creating the StatusExpression and TrendExpression is possible in Tabular Editor for both Analysis Services Tabular and Power BI data models. You might be able to create a TrendExpression for a Power Pivot model within Excel, however you can’t connect to it within Tabular Editor so I haven’t looked any further.

Because you are able to edit the expressions in Tabular Editor, you can create them in whichever form you’d like, although they should always evaluate to either [-2, -1, 0, 1, 2] for a 5 icon set, or [-1, 0, 1] for a 3 icon set for Power BI.

Below is an example for how you might accomplish a TrendExpression for your sales vs prior period sales. You can also achieve something similar using PARALLELPERIOD, however I’ll leave this up to you!

Sales KPI:=
IF ( [Sales Prior Period] = 0, BLANK (), [Sales] )
Sales Prior Period :=
VAR LastYear =
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Date' ),
            'Date'[Year] = MAX ( 'Date'[Year] ) - 1
        )
    )
VAR LastQuarter =
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Date' ),
            'Date'[QuarterIndex] = MAX ( 'Date'[QuarterIndex] ) - 1
        )
    )
VAR LastMonth =
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Date' ),
            'Date'[MonthIndex] = MAX ( 'Date'[MonthIndex] ) - 1
        )
    )
VAR LastWeek =
    CALCULATE (
        [Sales],
        FILTER (
            ALL ( 'Date' ),
            'Date'[WeekIndex] = MAX ( 'Date'[WeekIndex] ) - 1
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Date'[Week] ),
        LastWeek,
        IF (
            ISFILTERED ( 'Date'[Month] ),
            LastMonth,
            IF (
                ISFILTERED ( 'Date'[Quarter] ),
                LastQuarter,
                IF (
                    ISFILTERED ( 'Date'[Year] ),
                    LastYear,
                    BLANK ()
                )
            )
        )
    )
TrendExpression:=
VAR x = [Sales KPI] / [Sales Prior Period]
RETURN
    IF (
        ISBLANK ( x ),
        BLANK (),
        IF (
            x < 0.9,
            -1,
            IF (
                x < 1.1,
                0,
                1
            )
        )
    )

The above TrendExpression can be interpreted as a downwards trend if sales are down more than 10% over the prior period; stable if between -10% and +10% over the prior period; and upwards if over 10% from the prior period.

Note that when I create the base KPI measure I will usually make a minor adjustment to check for whether the target KPI measure is 0 to avoid divide by 0 errors.

KPI Divide by 0
Divide by 0 error on status expression result

Observation 3: Icon sets are based on the implementation within the reporting tool, and there is no universal standard for the graphic string identifiers.

What I mean by this is that each reporting tool decides on their own implementation of how they will interpret the values (e.g. -1, 0, and 1) received from the StatusExpression and TrendExpression.

Each client will then view the StatusGraphic [or TrendGraphic] string supplied by the KPI metadata, and if that string corresponds with an icon set, it will substitute the StatusExpression values for the corresponding icons.

From the Analysis Services protocol: “Because TrendGraphic [and StatusGraphic] is a string property, each client can define and use its own specific set of known values”.

The below table shows the graphic strings/icon sets I have found and their interpretations in Excel and Power BI. This is not an exhaustive list, feel free to tell me any you have found and I will update this post.

Power BI vs Excel Status Icons
Power BI vs Excel Status Icons

Below is the same list as included in the image above:

  • Cylinder
  • Faces
  • Five Bars Colored
  • Five Boxes Colored
  • Gauge
  • Gauge – Ascending
  • Gauge – Descending
  • Reversed Gauge
  • Reversed status arrow
  • Road Signs
  • Road Signs
  • Shapes
  • Shapes
  • Smiley
  • Smiley Face
  • Standard Arrow
  • Status Arrow
  • Thermometer
  • Three Triangles
  • Three Circles Colored
  • Three Flags Colored
  • Three Stars Colored
  • Three Symbols Uncircled Colored
  • Traffic Light
  • Traffic Light – Single
  • Variance Arrow
  • Status Arrow – Ascending
  • Status Arrow – Descending

Some notes about my testing

  • I did not test other client tools such as SSRS.
  • In both Excel and Power BI, all graphics are the same for StatusGraphic and TrendGraphic.
  • Excel inserts an icon-set conditional-format on top of the status number. This can be customized to whatever set of symbols/thresholds you want within the conditional formatting dialog.
  • Power BI strictly converts the StatusExpression results from -2,-1,0,1,2 to an icon set.
  • Excel uses a range to convert the StatusExpression results [for 5 levels]:
    • Level 1: x <-0.5
    • Level 2: -0.5 <= x < -0.01
    • Level 3: -0.01 <= x < 0.01
    • Level 4: 0.01 <= x < 0.5
    • Level 5: 0.5 <= x
  • Excel uses a range to convert the StatusExpression results [for 3 levels]:
    • Level 1: x <-0.5
    • Level 2: -0.5 <= x < 0.5
    • Level 3: 0.5 <= x
      Excel Conditional Format KPI
      Excel conditional formatting options on KPI graphics
  • Excel appears to default to red/yellow/green circles for strings it doesn’t recognize, whereas Power BI defaults to showing the status number.

Tips for developing KPIs in Tabular Editor (updated 3/1/2019)

  1. Create a new measure for your KPI that will evaluate to BLANK if the goal measure is blank or 0.
  2. If your goal measure evaluates to 0 (e.g. you have a goal to have 0 workplace injuries), use a “static” goal when creating your StatusExpression.
  3. Make your StatusExpression and TrendExpression evaluate to the correct number of icons as per the StatusGraphic/TrendGraphic you select. E.g. if you are using a 5-icon set, evaluate to -2,-1,0,1, or 2. If using 3-icon set, evaluate to -1,0, or 1.
  4. When using a 5-icon set and Excel, adjust the conditional format (within Excel) thresholds on the Status/Trend cells to be -2,-1,0, and 1.
  5. When adding measures in Power BI using Tabular Editor, don’t forget to delete the automatically generated Translated Name (as Power BI does not support this).

KPIs and their future implementation within Power BI

Knowing what we now do, I’m hoping that KPIs can be created natively within Power BI Desktop in the future, hopefully in the same implementation style as Excel.

There is clearly a demand for it based on the Power BI ideas request here, most likely it is an issue of technical difficulty versus the payoff.

My suggestion would be to implement it the same way as in Excel (although I understand there are technical difficulties and possibly even better ways to implement it!):

  1. Include a default icon set for unrecognized strings;
  2. Use a range rather than static values;
  3. Allow the icon set to be changed/customized within PBI.

Finally, I hope that @DOtykier might read this and update the Status Graphic and Trend Graphic property dropdowns to include the examples above that work! 🙏

And that’s it! I hope you found this post useful. Feel free to let me know any comments you may have.

One thought on “KPIs #2: Developing KPIs in Power BI and Analysis Services using Tabular Editor”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s