pbiplintRules / Error Prevention
Set IsAvailableInMdx to true on necessary columns
What it checks
Columns with IsAvailableInMdx set to false that are used to sort another column, appear in a hierarchy, a variation, or a calendar, or sort by another column.
Each finding names the column, as 'Date'[Month Number]. What the column is needed for is not in the line, so look for the column that sorts by it, the hierarchy level that names it, the variation that makes it a default, or the calendar that names it.
Example
table Date
column Date
dataType: dateTime
isKey
sourceColumn: Date
column 'Month Name'
dataType: string
sortByColumn: 'Month Number'
sourceColumn: MonthName
column 'Month Number'
dataType: int64
isHidden
isAvailableInMdx: false
sourceColumn: MonthNumber
table Date
column Date
dataType: dateTime
isKey
sourceColumn: Date
column 'Month Name'
dataType: string
sortByColumn: 'Month Number'
sourceColumn: MonthName
column 'Month Number'
dataType: int64
isHidden
sourceColumn: MonthNumber
Why it matters
A column that sorts another column, or sits in a hierarchy, is used through its attribute hierarchy, and that is exactly what setting IsAvailableInMdx to false removes. The result is a processing error, or a hierarchy that fails in Excel and other MDX clients, usually after someone set the property to false in bulk to save memory. A column a calendar names is read by time intelligence through the calendar, and a primary column also sorts the calendar's periods: "the primary columns are used for sorting" (Primary versus associated columns (opens in a new tab)).
How to fix it
Power BI Desktop has no setting for this property and never writes it, so the repair is in the TMDL file: delete the isAvailableInMdx: false line from under the column and the property goes back to its default of true. The other way to clear the same finding is to remove the need for the attribute hierarchy: take the column out of the hierarchy in Desktop's model view, or clear Sort by column under Column tools on the column that names it, and the rule stops reporting it. For a column a calendar names, take it out of the calendar. In the TMDL file, delete an associated or time-related column's line under its calendarColumnGroup. A primary column's line cannot go on its own, because "The primary column is required for each category" (Primary versus associated columns (opens in a new tab)): remove its whole calendarColumnGroup, or name another column as the group's primaryColumn. Calendar options in Table tools, which Desktop shows only once the Enhanced DAX Time Intelligence preview is turned on (Enable the enhanced DAX Time Intelligence preview (opens in a new tab)), makes the same changes for primary and associated columns, but not for a time-related column: Microsoft says tagging one "isn't currently possible in the calendar options, but can instead only be done using external tools or TMDL" (Available column categories (opens in a new tab)). Tabular Editor shows the property in its property grid, which is a quicker way to clear it across the batch of columns a single bulk edit set.
When to ignore it
There is no case for leaving it. A column reported here is one the engine needs an attribute hierarchy for, and the memory the property saves on it is small next to a model that fails to process. What is worth working out is why the property is there at all: it is almost always one bulk edit made across every hidden column, and the columns that needed the attribute hierarchy are the ones this rule lists. Clear those and leave the rest alone, so the saving stays where it does no harm.
To ignore this rule on one object, add annotation pbiplint.ignore = SET_ISAVAILABLEINMDX_TO_TRUE_ON_NECESSARY_COLUMNS under the object in its TMDL file. Power BI Desktop keeps the annotation. To turn the rule off for a whole project, set "SET_ISAVAILABLEINMDX_TO_TRUE_ON_NECESSARY_COLUMNS": "off" under rules in pbiplint.config.json.
Quirks
- A column with no
isAvailableInMdxline is true, because pbiplint applies that default wherever the property is absent. The line appears only where a tool wrote it, and the only value ever written isfalse, so this rule fires only where something set the property deliberately. - Variations are matched on the default column alone. pbiplint reads a variation's
defaultColumn, so a column a variation reaches only through its default hierarchy is not protected here. - Both ends of a sort-by pair are covered. The column that does the sorting is reported, and so is the column that names it in
sortByColumn, whenever the property is false on either. - A column a calendar names, as a primary, associated, or time-related column, is reported when IsAvailableInMdx is false, so the two rules stay mirrors. Tabular Editor 3's built-in version of the rule also reports a primary column a calendar names when its IsAvailableInMdx is false. The source rule does not read calendars.
Related rules
ISAVAILABLEINMDX_FALSE_NONATTRIBUTE_COLUMNSis the mirror of this rule: it reads hidden columns whose property is still true and that are used in none of these ways, and reports them as candidates for false. No column can be reported by both.MONTH_(AS_A_STRING)_MUST_BE_SORTEDasks for thesortByColumnthat brings a column into this rule's reach: the month number a month name sorts by is the column this rule then protects.UNNECESSARY_COLUMNSis the rule that reports a hidden column nothing reads. A column this rule lists because another column sorts by it, because it sits in a hierarchy, or because a calendar names it, is not reported there; one it lists only for a variation, or because it sorts by another column, can be, because that rule reads neither.
Ported from Microsoft's Best Practice Analyzer ruleset (opens in a new tab).
Check a model for this Improve this page (opens in a new tab)