Edit

Share via

INFO.VIEW.COLUMNS

Applies to: Calculated column Calculated table Measure Visual calculation

Returns a table with information about each column in the semantic model, such as name, description, and format string. This information helps you understand the model and to self-document the model when used in calculated tables.

Syntax

INFO.VIEW.COLUMNS()

Return value

A table with the following columns:

Column Description
[ID] The unique ID for each column in this semantic model as an integer.
[Name] The name of each column in this semantic model as a string.
[Table] The table of each column in this semantic model as a string.
[DataType] The data type of each column in this semantic model as a string.
[DataCategory] The data category of each column in this semantic model as a string.
[Description] The description of each column in this semantic model as a string.
[IsHidden] The hidden state of each column in this semantic model as True or False.
[IsUnique] The is unique of each column in this semantic model as True of False.
[IsKey] The is key of each column in this semantic model as True or False.
[IsNullable] The is nullable of each column in this semantic model as True or False.
[Alignment] The alignment of each column in this semantic model as a string.
[SummarizeBy] The summarize by of each column in this semantic model as a string.
[ColumnStorage] The column storage of each column in this semantic model as a string combination of name and ID.
[Type] The type of each column in this semantic model as a string.
[SourceColumn] The source column of each column in this semantic model as a string.
[Expression] The DAX formula of calculated columns.
[FormatString] The format string of each column in this semantic model as a string.
[IsAvailableInMDX] The is available in MDX of each column in this semantic model as True or False. Analyze in Excel pivot tables will only show columns set to True.
[SortByColumn] The sort by column of each column in this semantic model as a string. Shows as blank when sorting by itself.
[GroupingBehavior] The grouping behavior of each column in this semantic model as a string.
[SourceProviderType] The source provider type of each column in this semantic model as a string.
[DisplayFolder] The display folder of each column in this semantic model as a string. Nested folders shown with / and multiple folders separated by ;.
[AlternateOf] The alternate of property of each column in this semantic model as a string.
[LineageTag] The lineage tag of each column in this semantic model as a string.

Remarks

Unlike INFO.COLUMNS, this function can be used in calculated tables, columns, and measures of a semantic model, including as part of a model refresh.

Example 1 - DAX query

The following DAX query can be run in DAX query view:

EVALUATE
	INFO.VIEW.COLUMNS()

This DAX query returns a table with all of the columns of this DAX function.

Example 2 - DAX query with SELECTCOLUMNS and FILTER

The following DAX query can be run in DAX query view:

EVALUATE
  // Select specific columns from the filtered result
  SELECTCOLUMNS(
    // Filter columns from the INFO.VIEW.COLUMNS() table
    FILTER(
      INFO.VIEW.COLUMNS(),
      // Exclude rows where DataCategory is "RowNumber" and Table is "xTables"
      [DataCategory] <> "RowNumber" && [Table] <> "xTables"
    ),
    // Show only these selected columns with new names where specified
    [Table],
    "Column", [Name],
    [Description],
    "DAX formula", [Expression],
    [DataCategory],
    [DataType],
    [IsHidden]
  )
  // Order the result by Table and then by Column
  ORDER BY
    [Table], [Column]

This DAX query returns a table with only the specified columns and rows meeting the filter condition with a DAX formula.

Screenshot showing the output of INFO.VIEW.COLUMNS() with selected columns in DAX query view.

Example 3 - calculated table with SELECTCOLUMNS and FILTER

Either of the above examples work in a calculated table when the EVALUATE and ORDER BY keywords are removed and a table name added. Here is example 2 in a calculated table:

Columns in this semantic model = 
// Select specific columns from the filtered result
  SELECTCOLUMNS(
    // Filter columns from the INFO.VIEW.COLUMNS() table
    FILTER(
      INFO.VIEW.COLUMNS(),
      // Exclude rows where DataCategory is "RowNumber" and Table is "xTables"
      [DataCategory] <> "RowNumber" && [Table] <> "xTables"
    ),
    // Show only these selected columns with new names where specified
    [Table],
    "Column", [Name],
    [Description],
    "DAX formula", [Expression],
    [DataCategory],
    [DataType],
    [IsHidden]
  )

This calculated table shows the same information as the DAX query in example 2 in a table in the model itself.

Example 4 - measure

The following measure can be added to count the number of text columns in a semantic model:

Number of text columns = 
COUNTROWS(
    FILTER(
        INFO.VIEW.COLUMNS(),
            [DataType] = "Text"
    )
)

This will show a scalar value with the number of text columns in my model.

This can be shown in a visual:

Screenshot showing the output of INFO.VIEW.COLUMNS() with a measure then used in report visuals.

Or a DAX query in DAX query view:

DEFINE
	MEASURE 'Columns in this semantic model'[Number of text columns] = 
	COUNTROWS(
		FILTER(
			INFO.VIEW.COLUMNS(),
				[DataType] = "Text"
		)
	)

EVALUATE
	SUMMARIZECOLUMNS(
		"Number of text columns", [Number of text columns]
	)

See also

  • Last updated on