Daily DAX : Day 186 INFO.KPIS

INFO.KPIS Function Overview


    Purpose: Returns a list of all KPIs in the current Power BI model, with columns matching the schema rowset for KPI objects.

    Category: System/Information function.

    Availability: Supported in Power BI Desktop, Analysis Services, and Power Pivot environments. It was documented as of August 13, 2024, but may be considered undocumented or unsupported in some contexts due to limited Microsoft documentation.

    Limitations: Cannot be used in calculated tables or calculated columns, as is typical for INFO functions. It performs a context transition if called in a row context.


Syntax

DAX


INFO.KPIS ( [<RestrictionName> [, [<RestrictionValue>] [, <RestrictionName> [, [<RestrictionValue>] [, … ] ] ] ] )


    Parameters:

        RestrictionName: (Optional) The name of a column in the TMSCHEMA_KPIS schema to filter the results.

        RestrictionValue: (Optional) The value to filter the specified RestrictionName column.

        Multiple restriction pairs can be provided to further refine the output.

    Return Value: A table with one or more columns corresponding to the TMSCHEMA_KPIS schema, detailing metadata about KPIs in the model.


Columns in the Output Table

The table returned by INFO.KPIS includes columns from the TMSCHEMA_KPIS schema, which typically describe properties of KPIs such as:


    KPI Name: The name of the KPI.

    Measure Name: The base measure associated with the KPI.

    Goal Expression: The DAX expression or value defining the target/goal.

    Status Expression: The DAX expression determining the status (e.g., good, bad, neutral).

    Trend Expression: The DAX expression for the trend (e.g., improving, declining).

    Graphic: The visual indicator type (e.g., traffic light, thermometer) used for status or trend.

    Description: A string describing the KPI (if defined).

    Model ID: The identifier of the model containing the KPI.

    Other Metadata: Additional properties like format strings or state values (e.g., -1 for bad, 0 for neutral, 1 for good).


The exact columns depend on the TMSCHEMA_KPIS schema definition in the Tabular model.

Use Case

The INFO.KPIS function is primarily used for model introspection and documentation. It allows developers, analysts, or administrators to programmatically retrieve metadata about KPIs in a Power BI model, which can be useful in the following scenarios:


    Model Documentation:

        Generate a report listing all KPIs in a Power BI model, including their base measures, goals, and status expressions.

        Example: A data modeler wants to document all KPIs in a complex model to ensure consistency or share with stakeholders.

    DAX


    EVALUATE INFO.KPIS()


    This query returns a table listing all KPIs and their properties, which can be exported or visualized in a Power BI report.

    Auditing and Governance:

        Check which KPIs exist, their configurations, and whether they align with business requirements.

        Example: An administrator filters KPIs by a specific measure to verify correct setup.

    DAX


    EVALUATE INFO.KPIS("MEASURE_NAME", "Sales % Margin")


    This restricts the output to KPIs based on the "Sales % Margin" measure.

    Dynamic Reporting:

        Use INFO.KPIS in DAX queries to dynamically reference KPI metadata in reports or calculations.

        Example: Create a Power BI report that displays KPI names and their status graphics for a dashboard overview.

    Debugging and Maintenance:

        Identify misconfigured KPIs (e.g., missing goal expressions or incorrect status logic) during model development.

        Example: A developer uses INFO.KPIS to check if all KPIs have valid status expressions before deploying a model.

    Integration with External Tools:

        Export KPI metadata to external tools (e.g., Tabular Editor, DAX Studio) for advanced model management or automation.

        Example: Use INFO.KPIS in DAX Studio to script changes to KPI properties.


Practical Example

Suppose you have a Power BI model with KPIs defined for sales performance, such as "Sales % Margin" and "Revenue Growth." You want to list all KPIs and their properties.


    DAX Query:

    DAX


    EVALUATE

    INFO.KPIS()


    This returns a table with columns like:

        KPI_NAME: Sales % Margin KPI

        MEASURE_NAME: Sales % Margin

        GOAL_EXPRESSION: SUM(Targets[Goal])

        STATUS_EXPRESSION: IF([Sales % Margin] >= 0.1, 1, -1)

        GRAPHIC: TrafficLight

    Filtered Query:

    To list only KPIs related to a specific measure:

    DAX


    EVALUATE

    INFO.KPIS("MEASURE_NAME", "Sales % Margin")


    This returns only the KPI(s) tied to the "Sales % Margin" measure.

    Visualization in Power BI:

        Create a DAX query in DAX Studio, export the results to a CSV, and import them into Power BI.

        Alternatively, use a Power BI report to display the results of INFO.KPIS in a table visual for stakeholders.


Key Considerations


    Context Transition: If used in a row context, INFO.KPIS performs a context transition, which may affect performance in complex models.

    Unsupported Status: The function may lack full Microsoft documentation, so test thoroughly in your environment. Check compatibility notes on resources like dax.guide.

    Prerequisites: KPIs must be defined in the model (e.g., via Tabular Editor or Power BI Desktop) for INFO.KPIS to return results. KPIs are typically created on top of measures with goal, status, and trend properties.

    Performance: Since INFO.KPIS queries metadata, it’s lightweight but may slow down in models with many KPIs or complex restrictions.


How to Define KPIs for INFO.KPIS

If your model lacks KPIs, you can create them using tools like Tabular Editor:


    Open your Power BI model in Tabular Editor.

    Select a measure (e.g., "Sales % Margin").

    Define KPI properties:

        Goal: A DAX expression or static value (e.g., SUM(Targets[Goal])).

        Status: A DAX expression returning an integer (e.g., -1, 0, 1 for bad, neutral, good).

        Trend: A DAX expression for trend visualization (e.g., comparing current value to prior year).

        Graphic: Choose a visual indicator (e.g., TrafficLight, SmileyFace).

    Save and refresh the model.


Once KPIs are defined, INFO.KPIS can retrieve their metadata.

Comparison with Related Functions


    INFO.MEASURES: Returns metadata about all measures in the model, not specific to KPIs.

    Other INFO Functions: Functions like INFO.TABLES or INFO.COLUMNS provide metadata for other model objects but don’t focus on KPIs.


Limitations


    No Direct Visualization: INFO.KPIS returns metadata, not the KPI values or statuses. To visualize KPI results, use the underlying measures or KPI visuals.

    Tool Dependency: Defining KPIs often requires external tools like Tabular Editor, as Power BI Desktop’s UI lacks native KPI creation features.

    Restricted Use: Cannot be used in calculated columns or tables, limiting its scope to queries or measures.


Conclusion

The INFO.KPIS DAX function is a specialized tool for retrieving metadata about KPIs in a Power BI model. It’s invaluable for documentation, auditing, and dynamic reporting of KPI configurations. By returning a table aligned with the TMSCHEMA_KPIS schema, it provides insights into KPI names, measures, goals, statuses, and graphics. Its primary use cases include model governance, debugging, and integration with external tools. To leverage INFO.KPIS, ensure your model has KPIs defined, and use tools like DAX Studio or Tabular Editor for advanced scenarios.


Comments

Popular posts from this blog

Daily DAX : Day 65 INFO.TABLEPERMISSIONS

Daily DAX : Day 55 PV