Rules

72 rules: 66 ported from the Microsoft Best Practice Analyzer ruleset so the results match Tabular Editor, 5 listed but not run because they need statistics only a live model has, and 1 built into pbiplint. Ranked by severity, then category, then how many objects they hit.

Performance

• Avoid bi-directional relationships against high-cardinality columns warning needs a live model
  Columns in a bi-directional relationship that have more than 100,000 distinct values. Cardinality is not stored in the model files, so pbiplint lists this rule but cannot run it.
• Avoid excessive bi-directional or many-to-many relationships warning
  Models where bi-directional relationships plus many-to-many relationships make up more than 30 percent of all relationships. The finding is on the model, not on any one relationship.
• Avoid using many-to-many relationships on tables used for dynamic row level security error
  Regular tables that carry a row-level security filter in any role and take part in a many-to-many relationship.
• Check if bi-directional and many-to-many relationships are valid info
  Every relationship that is bi-directional, many-to-many, or both. This is a review list at info severity, not a defect.
• Check if dynamic row level security (RLS) is necessary info
  Row-level security filters that call USERNAME or USERPRINCIPALNAME. Reported per table permission, at info severity.
• Consider a star-schema instead of a snowflake architecture warning
  Tables that are on the from side of one relationship and the to side of another, which is what a dimension related to a sub-dimension looks like.
• Consider using aggregations if using Direct Query in Power BI info
  Models that have at least one DirectQuery table, no aggregation table (no column has an alternateOf mapping), and the PowerBI_V3 data source version, which is every project Desktop writes today.
• Date/calendar tables should be marked as a date table warning
  Tables with date or calendar in the name that are not marked as a date table, meaning the data category is not Time or no DateTime column is marked as the key.
• Do not use floating point data types warning
  Columns of any kind whose data type is Double, which Power BI Desktop calls Decimal Number.
• Large tables should be partitioned warning needs a live model
  Tables with more than 25 million rows and a single partition. Row counts are not in the model files, so pbiplint lists this rule but cannot run it.
• Limit row level security (RLS) logic warning
  Tables whose row-level security filter, in any role, calls RIGHT, LEFT, UPPER, LOWER, or FIND.
• Many-to-many relationships should be single-direction warning
  Many-to-many relationships with bi-directional cross filtering.
• Measures using time intelligence and model is using Direct Query warning
  Measures and calculation items that call a time intelligence function, in a model where at least one table is in DirectQuery mode.
• Minimize Power Query transformations warning
  Power Query partitions whose M text contains Table.Combine, Table.Join, Table.NestedJoin, Table.AddColumn, Table.Group, Table.Sort, Table.Pivot, Table.Unpivot, Table.UnpivotOtherColumns, Table.Distinct, a native SQL query, or an OLE DB or ODBC query.
• Model should have a date table warning
  Models with no table that has the data category Time and a DateTime column marked as the key, which is what Mark as date table sets.
• Reduce number of calculated columns warning
  Models with more than five calculated columns across all tables. Columns of calculated tables do not count, and the finding is on the model.
• Reduce usage of calculated columns that use the RELATED function warning
  Calculated columns whose DAX calls RELATED.
• Reduce usage of calculated tables warning
  Every calculated table. Calculation groups are not included.
• Reduce usage of long-length columns with high cardinality warning needs a live model
  Text columns where more than 500,000 rows hold values longer than 100 characters. Row data is not in the model files, so pbiplint lists this rule but cannot run it.
• Remove auto-date table warning
  Calculated tables generated by the Auto date/time option, whose names start with DateTableTemplate_ or LocalDateTable_.
• Remove redundant columns in related tables warning
  Columns that take part in no relationship and share a name with a column on a table that this table relates to from the many side. In practice, a fact table column that duplicates a dimension attribute.
• Set IsAvailableInMdx to false on non-attribute columns warning
  Hidden columns, or columns in hidden tables, that still have IsAvailableInMdx set to true and are not used to sort another column, in a hierarchy, or in a variation, and do not themselves sort by another column.
• Split date and time warning needs a live model
  DateTime columns holding values that are not at midnight. Row data is not in the model files, so pbiplint lists this rule but cannot run it.
• Unpivot pivoted (month) data warning
  Tables that have a numeric column for each of Jan, Feb, Mar, Apr, May, and Jun, matched as substrings of the column names.

Error Prevention

• Avoid invalid characters in descriptions error
  Descriptions containing a control character other than whitespace. Tabs and line breaks are allowed.
• Avoid invalid characters in names error
  Object names containing a control character other than whitespace. Tabs and line breaks are allowed here and are covered by SPECIAL_CHARS_IN_OBJECT_NAMES.
• Avoid structured data sources with provider partitions warning
  Partitions whose source is a legacy query, a provider partition, that points at a structured data source.
• Avoid the USERELATIONSHIP function and RLS against the same table error
  Tables that have a row-level security filter in any role and are named as the second argument of USERELATIONSHIP in a measure.
• Data columns must have a source column error
  Data columns with no source column. Calculated columns are not checked.
• Expression-reliant objects must have an expression error
  Measures, calculated columns, and calculation items whose expression is empty.
• Relationship columns should be of the same data type error
  Relationships whose two columns have different data types.
• Set IsAvailableInMdx to true on necessary columns error
  Columns with IsAvailableInMdx set to false that are used to sort another column, appear in a hierarchy or a variation, or sort by another column.
• TMDL could not be fully parsed error
  Lines the TMDL parser could not use: space indentation, an unterminated code fence, a line at an impossible indentation, a line in no form the parser recognizes, and a /// description with a blank line between it and its declaration.

DAX Expressions

• Avoid using '1-(x/y)' syntax warning
  Expressions with a number, then plus or minus, then either SUM('Table'[Column]) followed by a division operator, or a call to DIVIDE. The common shape is 1 - SUM(Sales[Cost]) / SUM(Sales[Amount]).
• Avoid using the IFERROR function warning
  Measures and calculated columns that call IFERROR.
• Column references should be fully qualified error
  Measures and row-level security filters that refer to a column by its bare name, [Column], instead of 'Table'[Column].
• Filter column values with proper syntax warning
  CALCULATE or CALCULATETABLE whose first filter argument is FILTER('Table', 'Table'[Column] ...).
• Filter measure values by columns, not tables warning
  CALCULATE or CALCULATETABLE whose first filter argument is FILTER('Table', [Measure] ...).
• Inactive relationships that are never activated warning
  Inactive relationships that no measure or calculation item activates with USERELATIONSHIP.
• Measure references should be unqualified error
  Measures, calculated columns, calculated tables, and calculation items that refer to a measure with a table prefix, 'Table'[Measure].
• Measures should not be direct references of other measures warning
  Measures whose whole expression is a reference to another measure, such as [Total Sales].
• No two measures should have the same definition warning
  Two or more measures whose DAX is identical once spaces, tabs, and line breaks are removed. Every copy is reported.
• The EVALUATEANDLOG function should not be used in production models info
  Measures that call EVALUATEANDLOG.
• Use the DIVIDE function for division warning
  Expressions that use the division operator right after a closing bracket or parenthesis, such as [Sales] / [Cost] or SUM(...) / SUM(...). A slash that starts a comment is ignored.
• Use the TREATAS function instead of INTERSECT for virtual relationships warning
  Measures and calculation items that call INTERSECT.

Maintenance

• Calculation groups with no calculation items warning
  Calculation groups that contain no calculation items.
• Ensure tables have relationships info
  Tables with no relationship to any other table. Calculation groups are not checked.
• Fix referential integrity violations warning needs a live model
  Relationships where the many side holds key values that do not exist on the one side. Row data is not in the model files, so pbiplint lists this rule but cannot run it.
• Perspectives with no objects info
  Perspectives that contain no tables. Adding any column, measure, or hierarchy to a perspective adds its table, so a perspective with no tables is empty.
• Remove data sources not referenced by any partitions info
  Data sources that no partition names and that appear nowhere in any partition's query text.
• Remove roles with no members info
  Roles with no members.
• Remove unnecessary columns warning
  Hidden columns, or columns in hidden tables, that nothing references: no DAX expression, relationship, hierarchy, sort-by column, row-level security filter, or object-level security rule.
• Remove unnecessary measures warning
  Hidden measures, or measures on hidden tables, that no DAX expression references.
• Visible objects with no description info
  Visible tables, columns, measures, and calculation groups with no description. Visibility is the object's own flag.

Formatting

• Add data category for columns info
  Columns with no data category whose name contains country, continent, or city and whose type is text, or whose name is exactly latitude or longitude and whose type is decimal or double.
• Do not summarize numeric columns error
  Visible whole number, decimal, or double columns whose default summarization is anything other than None.
• First letter of objects must be capitalized info
  Tables, measures, hierarchies, calculated columns, calculated tables, and calculation groups whose first character has an upper case form and is not upper case.
• Format flag columns as Yes/No value strings info
  Visible columns whose name starts with Is and whose type is whole number, and visible columns whose name ends with Flag and whose type is not text.
• Hide fact table columns warning
  Visible numeric columns that a measure aggregates directly with a fully qualified reference, such as SUM('Sales'[Amount]). COUNT, SUM, AVERAGE, MIN, MAX, DISTINCTCOUNT, VALUES, DISTINCT, and their A-suffixed variants count as aggregations.
• Hide foreign keys warning
  Visible columns whose name matches the from column of a relationship whose from side is many. Only the from cardinality is tested, so a many-to-many relationship counts here too, not just many-to-one.
• Mark primary keys info
  Columns on the one side of a relationship, outside date tables, that are not marked as the table's key.
• Month (as a string) must be sorted warning
  Text columns with month in the name, but not months, that have no sort-by column.
• Objects should not start or end with a space error
  Names that start or end with a space, for the model, tables, measures, hierarchies, perspectives, partitions, data columns, and calculated columns.
• Percentages should be formatted with thousands separators and 1 decimal warning
  Measures with a percent format string other than #,0.0%;-#,0.0%;#,0.0%.
• Provide format string for "Date" columns info
  DateTime columns with date in the name whose format string is not exactly mm/dd/yyyy.
• Provide format string for "Month" columns info
  DateTime columns with month in the name whose format string is not exactly MMMM yyyy.
• Provide format string for measures error
  Visible measures with no format string and no dynamic format string.
• Relationship columns should be of integer data type info
  Any column that takes part in a relationship and is not a whole number.
• Whole numbers should be formatted with thousands separators and no decimals warning
  Measures whose static format string is not a recognized whole-number, currency, or percentage format. The only format strings the rule accepts are #,0, #,0.0, and any string containing $ or %. A measure with no format string at all fires too, and that is the common case: the rule reads only the format string, so it cannot tell an unformatted currency or ratio from an unformatted count. Each finding says what the rule saw: no format string, format string "0.00", or dynamic format string only.

Naming Conventions

• Object names must not contain special characters warning
  Names containing a tab, line feed, or carriage return.
• Partition name should match table name for single partition tables info
  Regular tables with exactly one partition whose name differs from the table name. Calculated tables and calculation groups are not checked.
• Trim object names info
  Names that start or end with a space, across every named object type in the model.