Rules

122 rules: 66 rules ported from Microsoft's Best Practice Analyzer ruleset and verified against Tabular Editor, 5 listed but not run because they need statistics only a live model has, 11 report rules ported from PBI Inspector's base rules by Nat Van Gulck, and 40 built into pbiplint. Ranked by severity, then category, then how many objects they hit.

Performance

• Avoid bi-directional relationships against high-cardinality columns warning model needs a live model
  Columns in a bi-directional relationship that have more than 100,000 distinct values. Cardinality is a statistic of the loaded data, not of the model files, so pbiplint lists this rule but does not run it: it needs column statistics that only a live model carries.
• Avoid excessive bi-directional or many-to-many relationships warning model
  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 setting ‘Show items with no data’ on columns warning report
  Visuals with Show items with no data turned on for any of their field wells.
• Avoid using many-to-many relationships on tables used for dynamic row level security error model
  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 model
  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 model
  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 model
  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 model
  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 model
  Tables with date or calendar in the name that define no calendar and 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 model
  Columns of any kind whose data type is Double, which Power BI Desktop calls Decimal Number.
• Large tables should be partitioned warning model needs a live model
  Tables with more than 25 million rows and a single partition. The row count is a statistic of the loaded data, not of the model files, so pbiplint lists this rule but does not run it: it needs statistics that only a live model carries.
• Limit row level security (RLS) logic warning model
  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 model
  Many-to-many relationships with bi-directional cross filtering.
• Measures using time intelligence and model is using Direct Query warning model
  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
  Power Query partitions whose M names Table.Combine, Table.Join, Table.NestedJoin, Table.AddColumn, Table.Group, Table.Sort, Table.Pivot, Table.Unpivot, Table.UnpivotOtherColumns, Table.Distinct, Value.NativeQuery, OleDb.Query, or Odbc.Query, or gives a database connector a native SQL query in its Query option.
• Model should have a date table warning model
  Models with no table that defines a calendar, and none that has the data category Time and a DateTime column marked as the key, which is what Mark as date table sets.
• ODBC or OLE DB where Power BI has a connector info Power Query
  ODBC and OLE DB sources whose connection string names the driver or provider of a database Power BI has its own connector for: Amazon Redshift, PostgreSQL, SQL Server, MySQL, Oracle, or Snowflake.
• Reduce number of calculated columns warning model
  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 number of pages per report warning report
  Reports with more pages than the threshold, 10 by default, hidden pages included.
• Reduce the number of objects within visuals warning report
  Visuals with more entries in their field wells than the threshold, 6 by default, counting each column, measure, visual calculation, or sparkline in any of the visual's wells as one.
• Reduce the number of visible visuals on the page warning report
  Pages with more visuals than the threshold, 20 by default, counting every visual except shapes, slicers, buttons, text boxes, and a visual hidden by isHidden in its own visual.json.
• Reduce usage of Advanced filtering visuals by page warning report
  Pages with more visuals carrying an Advanced filter with a condition applied than the threshold, 4 by default.
• Reduce usage of calculated columns that use the RELATED function warning model
  Calculated columns whose DAX calls RELATED.
• Reduce usage of calculated tables warning model
  Every calculated table. Calculation groups are not included.
• Reduce usage of long-length columns with high cardinality warning model needs a live model
  Text columns where more than 500,000 rows hold values longer than 100 characters. That count is a statistic of the loaded data, not of the model files, so pbiplint lists this rule but does not run it: it needs statistics that only a live model carries.
• Reduce usage of TopN filtering visuals by page warning report
  Pages with more visuals carrying a Top N filter than the threshold, 4 by default.
• Remove auto-date table warning model
  Calculated tables generated by the Auto date/time option, whose names start with DateTableTemplate_ or LocalDateTable_.
• Remove custom visuals which are not used in the report warning report
  Custom visuals from AppSource that the report registers in report.json and that no visual on any page uses.
• Remove redundant columns in related tables warning model
  Columns that take part in no relationship and share a name with a column on a table at the to side of a relationship from their own table.
• Set IsAvailableInMdx to false on non-attribute columns warning model
  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, in a variation, or in a calendar, and do not themselves sort by another column.
• Split date and time warning model needs a live model
  DateTime columns holding values that are not at midnight. Whether any row carries a time is a statistic of the loaded data, not of the model files, so pbiplint lists this rule but does not run it: it needs statistics that only a live model carries.
• Unpivot pivoted (month) data warning model
  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

• Action points at nothing error report
  Buttons, shapes, and images whose page navigation, drillthrough, or bookmark action names a page or a bookmark the report does not have, matched against the name in each page.json and bookmark file.
• Avoid invalid characters in descriptions error model
  Descriptions containing a control character other than whitespace. Tabs and line breaks are allowed.
• Avoid invalid characters in names error model
  Object names containing a control character that is not whitespace. The whitespace control characters are left out of the test; SPECIAL_CHARS_IN_OBJECT_NAMES reads names for a tab, a line feed, and a carriage return.
• Avoid structured data sources with provider partitions warning model
  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 model
  Tables that have a row-level security filter in any role and are named as the second argument of USERELATIONSHIP in a measure.
• Bookmark refers to a missing page or visual warning report
  Bookmarks whose active page, or another page they capture, is not in the report, which capture a visual that is not on its page, or which apply only to selected visuals and name one that is not on their active page, matched against the name in each page.json and visual.json.
• Data columns must have a source column error model
  Data columns with no source column. Calculated columns are not checked.
• Data source the service can't refresh error Power Query
  Data sources whose address the Power BI service cannot know before the query runs: a call such as Web.Contents, File.Contents, or Sql.Database whose first argument is read from a table's rows, built by a function call, chosen with if ... then ... else, or a custom function's parameter.
• Excel headers promoted by the workbook reader info Power Query
  Excel.Workbook calls that ask the workbook reader to promote headers: true as the second argument, or UseHeaders = true in its options record.
• Expression-reliant objects must have an expression error model
  Measures, calculated columns, and calculation items whose expression is empty.
• Field the model does not have error project
  References in the report to a table, column, measure, hierarchy, or hierarchy level that the model does not have, wherever the report names a field: a visual's wells, formatting, and sort, a filter on a visual, a page, or the whole report, a drillthrough or tooltip page's fields, and a bookmark.
• File could not be fully parsed error project
  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, a line at the root of a file that TMDL does not allow there (a misspelt table, a column or a property that lost its tabs, or an annotation with lines under it, for example), much the same directly under a model, where TMDL lets the model's tables, relationships, and other objects sit indented (a property that lost its tabs is reported there only when lines sit under it, and a flag such as isHidden only when a declaration does, since the model has properties and flags of its own), a declaration under a database other than its model, a table line under anything but a model, which a stray tab puts under the declaration above it, a declaration that its object does not hold, such as a misspelt columm under a table or a hierarchy's level that lost a tab, a declaration such as table or role written with no name, a name not enclosed in single quotes as TMDL requires, such as table 'Date, and a /// description with a blank line between it and its declaration; a report JSON file that is not valid JSON, carries a merge conflict marker, or nests more than 256 levels deep; a report file Microsoft publishes a schema for, such as report.json or a page.json, whose content is not a JSON object; and a reportExtensions.json that lists no entity, or holds an entity with no measure, which Power BI Desktop does not open. It also covers M that Microsoft's Power Query parser cannot read, in a partition's source, a shared expression, or an incremental refresh policy's source expression, at the line and column where the parser stopped.
• File extension filter misses other cases warning Power Query
  Power Query filters on a folder's files that compare the Extension column with text such as ".csv" as written, so a file whose extension is in other capitals, such as .CSV, is left out.
• Incremental refresh filter counts a boundary row twice or never error Power Query
  Power Query queries that filter on both of the model's incremental refresh parameters, RangeStart and RangeEnd, and either include the boundary on both (>= RangeStart and <= RangeEnd) or on neither (> RangeStart and < RangeEnd).
• Keep First Rows left in a loaded query warning Power Query
  Power Query queries that load into the model and keep only their first rows: a Table.FirstN call with a number greater than 1, the Keep First Rows step, whose table is not sorted first.
• Legacy Excel workbook info Power Query
  Power Query queries that read an .xls or .xlsb workbook, which Power Query opens through the Access Database Engine rather than reading the file itself.
• Opening page missing or hidden error report
  A pages.json whose landing page names a page the report does not have, or, when no landing page is set, whose active page names a page the report does not have or a page hidden from readers.
• Path on a local drive warning Power Query
  Text in a Power Query query or parameter that starts with a path on a drive letter, such as C:\Demo\Data\Sales.csv or D:/Exports/.
• Privacy levels ignored for this file warning Power Query
  A model whose Power Query options ignore privacy levels: the fastCombine flag in the dataAccessOptions block of model.tmdl, which Power BI Desktop writes when Ignore the Privacy Levels and potentially improve performance is chosen for the file.
• Python or R script in a query warning Power Query
  Power Query queries that run a Python or R script: a Python.Execute or R.Execute call, which the Run Python script and Run R script steps write.
• Relationship columns should be of the same data type error model
  Relationships whose two columns have different data types.
• Rule could not finish error project
  A rule that stopped partway through an object or query, because pbiplint met input it could not handle. Each finding names the rule, the object or query, and the kind of error. This is a bug in pbiplint, not a fault in the model, and the rule's findings on that object or query are missing.
• Secret written into a query error Power Query
  Credentials written into Power Query queries as text: a key, token, or password in a web request's headers or Query record, in a web address, as a Bearer token, or in an ODBC or OLE DB connection string.
• Set IsAvailableInMdx to true on necessary columns error model
  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.
• Sort may not decide which duplicate is kept warning Power Query
  Power Query queries that remove duplicates by some of their columns straight after a sort, with nothing holding the sort's order: a Table.Distinct call with a list of columns whose table is a Table.Sort, written in place or as the step it reads. Power BI Desktop writes this shape as a Sorted Rows step followed by Removed Duplicates.
• User-defined function with a one-word name info model
  User-defined functions whose name holds neither a dot nor an underscore, such as AddTax. Tabular Editor 3 has a built-in rule with the same test.

Accessibility

• Ensure alternativeText has been defined for all visuals warning report
  Visuals other than shapes whose alt text is missing or empty. A visual group is checked by its own alt text, the one set on the group rather than on the visuals inside it.
• Tab order disagrees with the layout warning report
  Pages whose tab order, the order keyboard users move through the visuals in, disagrees with the order the layout reads in: rows from top to bottom, and left to right within a row. The rule checks this only when the project's policy asks for tab order to follow the layout, and reports nothing without it.

DAX Expressions

• Avoid using '1-(x/y)' syntax warning model
  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 model
  Measures and calculated columns that call IFERROR.
• Column references should be fully qualified error model
  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 model
  CALCULATE or CALCULATETABLE whose first filter argument is FILTER('Table', 'Table'[Column] ...).
• Filter measure values by columns, not tables warning model
  CALCULATE or CALCULATETABLE whose first filter argument is FILTER('Table', [Measure] ...).
• Hardcoded period in DAX info model
  Measures, calculated columns, and calculation items whose DAX fixes a year or a date, and date tables whose CALENDAR ends on a fixed date.
• Inactive relationships that are never activated warning model
  Inactive relationships that no measure, calculation item, or user-defined function activates with USERELATIONSHIP.
• Measure references should be unqualified error model
  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 model
  Measures whose whole expression is a reference to another measure, such as [Total Sales].
• No two measures should have the same definition warning model
  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 model
  Measures that call EVALUATEANDLOG.
• Use the DIVIDE function for division warning model
  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 model
  Measures and calculation items that call INTERSECT.

Maintenance

• Calculation groups with no calculation items warning model
  Calculation groups that contain no calculation items.
• Ensure tables have relationships info model
  Tables with no relationship to any other table. Calculation groups are not checked.
• Fix referential integrity violations warning model needs a live model
  Relationships where the many side holds key values that do not exist on the one side. The count of offending rows is a statistic of the loaded data, not of the model files, so pbiplint lists this rule but does not run it: it needs statistics that only a live model carries.
• Fixed date in a row filter info Power Query
  Power Query row filters that end at, or match, a date written into the query: a Table.SelectRows condition that compares a column with <, <=, or = to a #date, #datetime, or #datetimezone of fixed numbers.
• Hidden visual with fields bound info report
  Visuals hidden in the Selection pane, with their own eye icon or with that of a group they sit in, that still have fields in their wells.
• Measure defined in the report warning report
  Measures defined in the report's reportExtensions.json rather than in the model, reported when the model the report reads is in the input, so that each can move into it.
• Not reached from the report info project
  Columns and measures that nothing in the report reaches, directly or through the model. The walk starts from every field the report names, both columns of every relationship except one to an auto date/time table, the columns and measures that row-level security filters name, the columns that object-level security names, the default column of every variation, the columns a calendar names, the columns of an aggregation table (the ones with an alternateOf mapping), the fields the report's own measures reference, and the user-defined functions those measures and the security filters call, and it follows DAX references, calls to user-defined functions, sort-by and group-by columns, the detail column or table each mapping names, and calculated tables until nothing new is reached.
• Perspectives with no objects info model
  Perspectives that contain no tables. The rule reads only a perspective's table entries, which is enough: each column, measure, and hierarchy a perspective includes sits under the entry for its table, so with no table entry it includes nothing.
• Remove data sources not referenced by any partitions info model
  Data sources that no partition names and that appear nowhere in any partition's query or M text.
• Remove roles with no members info model
  Roles with no members.
• Remove unnecessary columns warning model
  Hidden columns, or columns in hidden tables, that nothing references: no DAX expression, relationship, hierarchy, sort-by column, group-by column, calendar, row-level security filter, or object-level security rule.
• Remove unnecessary measures warning model
  Hidden measures, or measures on hidden tables, that no DAX expression references.
• Server name written into the query info Power Query
  Power Query queries that name a database server as text in a connector such as Sql.Database, rather than reading it from a parameter, reported once per query.
• Steps keep Power Query's default names info Power Query
  Power Query queries whose steps keep the names Power BI Desktop gives them, such as Changed Type, Added Custom1, or Removed Other Columns, reported once per query.
• User-defined function nothing calls info model
  User-defined functions that no measure, calculated column, calculated table, calculation item, row-level security filter, format string expression, KPI, or other function in the model calls. The functions of a DAX Lib package, which share one DAXLIB_PackageId annotation, count as one: the package is reported once, on its first function, when nothing outside it calls any of them.
• User-defined function with no description info model
  User-defined functions with no description, or one of only spaces, other than functions installed from a DAX Lib package. Tabular Editor 3 has a built-in rule with the same test, which also reports package functions.
• Visible objects with no description info model
  Visible tables, columns, measures, and calculation groups with no description. Visibility is the object's own flag.

Report Design

• Action has no destination warning report
  Buttons, shapes, and images whose page navigation, drillthrough, or bookmark action is switched on but has no destination: the property that holds the destination for the action's type is missing from visual.json or empty.
• Data visual with no fields warning report
  Data visuals with no field in any of their wells. Every visual type counts as a data visual, custom visuals included, except visual groups and the types that need no fields by design, such as shapes, text boxes, images, and buttons.
• Ensure charts use theme colours warning report
  Visuals other than text boxes with a colour property set to a hex value instead of a theme colour, including a hex value inside a conditional formatting rule or a gradient.
• Ensure pages do not scroll vertically warning report
  Visible pages taller than the threshold, 720 pixels by default.
• Filters pane state differs from policy warning report
  A report whose Filters pane, as saved in report.json, is not in the state the project's policy expects, open or closed. Without a policy the rule reports nothing.
• Hardcoded year in a filter info report
  Filters in the Filters pane, on a visual, a page, or all pages, that hold a year column to fixed years: years picked in Basic filtering, a year set with Advanced filtering's is, years kept up to one with is less than or is less than or equal to, and a range between two years.
• No landing page set info report
  A report whose pages.json sets no landing page, so it opens on the page that was active when it was last saved, or, when pages.json records no active page either, on the first page.
• Page keeps its default name warning report
  Pages whose display name in page.json has the shape Page <n>, Duplicate of <name>, or <name> (copy): the names English Power BI Desktop gives a new page and a duplicated one, and a name marked as a copy. The rule also matches the forms Desktop-saved files show for a new or duplicated page in other languages, such as Seite <n> and Doublon de <name>.
• Slicer saved with a search term warning report
  Slicers saved with a term in their search box: any visual whose visual.json holds a selfFilter with a condition under objects.general, which is where Power BI Desktop's saved files keep the text typed in a slicer's search box.
• Slicer saved with a selection info report
  Slicers saved with a selection, so that the report opens with it applied: any visual whose visual.json holds a filter with a condition under objects.general, which is where Power BI Desktop saves the selection of a slicer, button slicer, list slicer, input slicer, or filterSlicer, and of a custom slicer from AppSource that filters through the Visual Filters API. It is info without a policy, and a warning when the project's policy expects no saved selections.
• Tooltip and Drillthrough pages should be hidden warning report
  Tooltip pages and drillthrough pages that are not hidden.
• Visual extends past the page warning report
  Visuals whose box runs past the right or bottom edge of their page by a pixel or more: x plus width beyond the page's width, or y plus height beyond its height.

Formatting

• Add data category for columns info model
  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 model
  Visible whole number, decimal, or double columns whose default summarization is anything other than None.
• First letter of objects must be capitalized info model
  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 model
  Visible columns whose name starts with Is and whose type is whole number, and visible columns whose name ends with the word Flag after a space and whose type is not text.
• Hide fact table columns warning model
  Visible numeric columns that a measure aggregates directly with a fully qualified reference, such as SUM('Sales'[Amount]). COUNT, COUNTBLANK, SUM, AVERAGE, MIN, MAX, DISTINCTCOUNT, VALUES, DISTINCT, and the A-suffixed COUNTA, AVERAGEA, MAXA, and MINA count as aggregations.
• Hide foreign keys warning model
  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 model
  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 model
  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 model
  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 model
  Measures with a percent format string other than #,0.0%;-#,0.0%;#,0.0%.
• Provide format string for "Date" columns info model
  DateTime columns with date in the name whose format string is not exactly mm/dd/yyyy.
• Provide format string for "Month" columns info model
  DateTime columns with month in the name whose format string is not exactly MMMM yyyy.
• Provide format string for measures error model
  Visible measures with no format string and no dynamic format string, other than those whose DAX plainly returns text.
• Relationship columns should be of integer data type info model
  Any column that takes part in a relationship and is not a whole number.
• Visible decimal column with no format string info model
  Visible Decimal number and Fixed decimal number columns with no format string.
• Whole numbers should be formatted with thousands separators and no decimals warning model
  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.

Naming Conventions

• Object names must not contain special characters warning model
  Names containing a tab, line feed, or carriage return.
• Partition name should match table name for single partition tables info model
  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 model
  Names that start or end with a space, across every named object type in the model.
• Visible name with no translation info model
  Visible tables, columns, measures, and hierarchies, and the levels of visible hierarchies, that a translated culture other than the model's own gives no caption.