Daily DAX : Day 281 INFO.PERSPECTIVECOLUMNS

 The **INFO.PERSPECTIVECOLUMNS** function in Power BI's Data Analysis Expressions (DAX) is part of the suite of INFO functions introduced to provide metadata about a Power BI semantic model. Specifically, **INFO.PERSPECTIVECOLUMNS** retrieves information about the columns included in a perspective within the model. Perspectives in Power BI are subsets of a model’s objects (tables, columns, measures) designed to simplify the user experience by focusing on specific areas of the model for reporting or analysis.

### Syntax

```

INFO.PERSPECTIVECOLUMNS()

```

- **No arguments**: This function does not take any parameters and returns a table containing metadata about columns in perspectives defined in the model.

### Return Value

The function returns a table with the following columns:

- **PerspectiveID**: A unique identifier (GUID) for the perspective.

- **PerspectiveName**: The name of the perspective.

- **TableID**: The unique identifier for the table containing the column.

- **ColumnID**: The unique identifier for the column.

- **Name**: The name of the column.

- **IsHidden**: Indicates whether the column is hidden in the perspective (TRUE/FALSE).

- **ModifiedTime**: The timestamp when the column was last modified.

- **StructureModifiedTime**: The timestamp when the column’s structure was last modified.

### Use Case

**INFO.PERSPECTIVECOLUMNS** is primarily used for **model documentation**, **auditing**, and **management** of Power BI semantic models, particularly in complex environments with multiple perspectives. Here are some practical scenarios where this function is useful:

1. **Documenting Perspectives in a Model**:

   - When working with large or complex Power BI models, perspectives help simplify the user interface by exposing only relevant tables and columns for specific use cases (e.g., sales analysis, HR reporting). **INFO.PERSPECTIVECOLUMNS** allows developers or administrators to generate a list of all columns included in each perspective, making it easier to document which columns are available to end-users in a given perspective.

   - Example: A BI team can run a DAX query using **INFO.PERSPECTIVECOLUMNS** to create a report or export a list of columns in the "Sales Perspective" to share with stakeholders for validation.

2. **Auditing and Governance**:

   - Organizations with strict data governance policies can use this function to verify which columns are exposed in perspectives and ensure compliance with data access rules. For instance, checking if sensitive columns (e.g., employee salaries) are hidden or excluded from certain perspectives.

   - Example: A data steward can query **INFO.PERSPECTIVECOLUMNS** to confirm that only approved columns are visible in a perspective used by external partners.

3. **Troubleshooting and Model Optimization**:

   - When troubleshooting issues in a model, developers can use **INFO.PERSPECTIVECOLUMNS** to understand the structure of perspectives, such as identifying hidden columns or verifying if a column is correctly included in the intended perspective.

   - Example: If a report based on a perspective is missing expected data, a developer can use this function to check if the required columns are included and not hidden.

4. **Integration with External Tools**:

   - The metadata retrieved by **INFO.PERSPECTIVECOLUMNS** can be exported to tools like Excel or SQL Server Management Studio (SSMS) for further analysis or documentation. This is particularly useful for maintaining an inventory of model components.

   - Example: A BI developer can copy the results of a DAX query using **INFO.PERSPECTIVECOLUMNS** into Excel to create a dynamic table for tracking perspective configurations.

### Example DAX Query

To retrieve information about columns in all perspectives in a Power BI model, you can use the following DAX query in DAX Query View:

```dax

EVALUATE

INFO.PERSPECTIVECOLUMNS()

```

This query will return a table listing all columns in all perspectives, including their names, IDs, and visibility status. To make the output more actionable, you can join it with other INFO functions, such as **INFO.TABLES**, to get table names instead of TableIDs:

```dax

EVALUATE

SELECTCOLUMNS(

    NATURALLEFTOUTERJOIN(

        INFO.PERSPECTIVECOLUMNS(),

        SELECTCOLUMNS(

            INFO.TABLES(),

            "TableID", [ID],

            "TableName", [Name]

        )

    ),

    "PerspectiveName", [PerspectiveName],

    "TableName", [TableName],

    "ColumnName", [Name],

    "IsHidden", [IsHidden]

)

```

This query joins **INFO.PERSPECTIVECOLUMNS** with **INFO.TABLES** to include table names in the output, making it easier to understand which columns belong to which tables in each perspective.

### Practical Example

Suppose you have a Power BI model with a perspective named "Finance Perspective" that includes only financial-related tables and columns, such as "Revenue," "Expenses," and "Profit." You want to document which columns are included in this perspective and whether any are hidden. You can run the above DAX query in Power BI Desktop’s DAX Query View. The result might look like:

| PerspectiveName     | TableName | ColumnName | IsHidden |

|--------------------|-----------|------------|----------|

| Finance Perspective| Financials| Revenue    | FALSE    |

| Finance Perspective| Financials| Expenses   | FALSE    |

| Finance Perspective| Financials| Profit     | TRUE     |

This output shows that the "Profit" column is hidden in the "Finance Perspective," which might prompt you to investigate whether this was intentional or needs correction.

### Key Benefits

- **Native DAX Integration**: Unlike traditional Dynamic Management Views (DMVs), **INFO.PERSPECTIVECOLUMNS** is a native DAX function, accessible directly in Power BI Desktop’s DAX Query View with IntelliSense support.

- **Joinable with Other Functions**: You can combine it with other DAX functions (e.g., **SELECTCOLUMNS**, **NATURALLEFTOUTERJOIN**) to customize the output.

- **Simplifies Model Management**: It streamlines the process of understanding and documenting perspective configurations without needing external tools like DAX Studio.

### Limitations

- **Tabular Output**: As a tabular function, **INFO.PERSPECTIVECOLUMNS** returns a table, so it’s best used in DAX Query View or for creating calculated tables. It cannot be used directly in measures or calculated columns.

- **Perspective Dependency**: The function is only relevant if your model uses perspectives. If no perspectives are defined, the function returns an empty table.

- **DirectQuery Compatibility**: Some INFO functions may have limitations in DirectQuery mode, so verify compatibility if using this storage mode.

### Source

The information about **INFO.PERSPECTIVECOLUMNS** is derived from the Microsoft Power BI Blog and related DAX documentation, specifically from updates introduced in the December 2023 Power BI Desktop release.[](https://powerbi.microsoft.com/en-us/blog/dax-query-view-introduces-new-info-dax-functions/)[](https://learn.microsoft.com/en-us/dax/dax-function-reference)

By using **INFO.PERSPECTIVECOLUMNS**, Power BI developers and analysts can efficiently manage and document their models, ensuring clarity and compliance in complex reporting environments.