locked
Dynamic KPIs on SSAS Tabular Model RRS feed

  • Question

  • Question on Tabular Model KPIs. Can I have dynamic KPIs in SSAS Tabular model? I have a requirement where the end user needs the capability to set the KPI values (For ex. the user should be able to adjust the threshold and set goals). Based on the set value I need to refresh my model so that my power view updates with the latest data.

    In my model in Visual Studio I see I can only set the KPI values thru UI and there is no provision to have DAX queries there. Is there any other way to do this in a tabular model?

    Wednesday, February 19, 2014 5:59 AM

Answers

  • Hi Maxpayne,

    There are some differences between Analysis Services OLAP and Tabular. On Key Performance Indicators (KPI) functionality, SSAS OLAP has a adventure that user can define more advanced KPIs (using five-status gauges) with optional trend-based components. The issue with SSAS Tabular is that KPIs do not have a trend-based element, which means the developer must create separate calculations for the trend-based portions of a KPI.

    When defining a KPI, in Define target value, select from one of the following:

    • Select Measure, and then select a target measure from the listbox.
    • Select Absolute value, and then type a numerical value.

    So we cannot warite a DAX query to define the target value.

    Reference:
    The Baker’s Dozen: 13 Differences Between Analysis Services OLAP and Tabular

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Michael Amadi Monday, February 24, 2014 10:42 PM
    • Marked as answer by Charlie Liao Monday, March 3, 2014 6:55 AM
    Thursday, February 20, 2014 6:15 AM

All replies

  • Hi Maxpayne,

    There are some differences between Analysis Services OLAP and Tabular. On Key Performance Indicators (KPI) functionality, SSAS OLAP has a adventure that user can define more advanced KPIs (using five-status gauges) with optional trend-based components. The issue with SSAS Tabular is that KPIs do not have a trend-based element, which means the developer must create separate calculations for the trend-based portions of a KPI.

    When defining a KPI, in Define target value, select from one of the following:

    • Select Measure, and then select a target measure from the listbox.
    • Select Absolute value, and then type a numerical value.

    So we cannot warite a DAX query to define the target value.

    Reference:
    The Baker’s Dozen: 13 Differences Between Analysis Services OLAP and Tabular

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.


    Charlie Liao
    TechNet Community Support

    • Proposed as answer by Michael Amadi Monday, February 24, 2014 10:42 PM
    • Marked as answer by Charlie Liao Monday, March 3, 2014 6:55 AM
    Thursday, February 20, 2014 6:15 AM
  • Hi

    I realize this may not have been available at the time of the post, however it is now, using Azure Analysis Services - Compatibility level SQL Server 2016 RTM (1200) as shown below 

    1. Created a Measure called Sales Reported
    2. Created a measure called Sales Reported KPI Goal (using DAX)
    3. Set the KPI Target to Sale Reported KPI Goal
    Thursday, August 24, 2017 4:55 AM