Here’s a situation I’ve found myself in many times. You’re called to help someone whose Power BI report is suddenly very slow. You ask whether any changes were made to the semantic model around the time performance got worse and the answer is no, absolutely not. You then spend anything from a few hours to a few days trying to fix the problem and when you find it, it turns out that someone did make a ‘minor’ change to the semantic model after all and this was the cause of it all. Wouldn’t it be good if you could see when things like measures were last changed? Well you can.
Many of the DAX INFO functions, which allow you to query semantic model metadata, have a ModifiedTime column which tells you the date and time that an object was last changed. So for example the INFO.MEASURES() (but not INFO.VIEW.MEASURES() ) function will tell you the date and time each measure in your semantic model was modified. You can query it like so from either the DAX query view in Desktop or the Service, or DAX Studio:
EVALUATESELECTCOLUMNS ( INFO.MEASURES (), "Name", [Name], "Last Modified", [ModifiedTime], "Expression", [Expression])ORDER BY [Last Modified] DESC
Here’s an example of the output of this query:
Of course if you’re troubleshooting a problem you will want to look at more than just the measures. I therefore put together this rather lengthy DAX query which looks at measures, calculated columns, calculation groups and lots of other things and gives you a list of all the things that have been modified, sorted in descending order by modification date:
DEFINE /* Set to NOW() - 7 to see only the last week of changes */ VAR _ChangedSince = DATE(1900, 1, 1) /* Lookups reused across sections */ VAR _Tables = SELECTCOLUMNS(INFO.TABLES(), "TableID", [ID], "TableName", [Name]) VAR _Roles = SELECTCOLUMNS(INFO.ROLES(), "RoleID", [ID], "RoleName", [Name]) VAR _Perspectives = SELECTCOLUMNS(INFO.PERSPECTIVES(), "PerspectiveID", [ID], "PerspectiveName", [Name]) /* Joined by the definition's own ObjectID: older models have no FormatStringDefinitionID column */ VAR _FormatStringDefs = SELECTCOLUMNS(INFO.FORMATSTRINGDEFINITIONS(), "OwnerID", [ObjectID], "ModifiedTime", [ModifiedTime], "Expression", [Expression]) /* Detail rows run on drillthrough / show-as-table; joined by ObjectID like format strings */ VAR _DetailRowsDefs = SELECTCOLUMNS(INFO.DETAILROWSDEFINITIONS(), "OwnerID", [ObjectID], "ModifiedTime", [ModifiedTime], "Expression", [Expression]) /* Bi-directional relationships appear once per direction: keep one row per relationship */ VAR _RelNames = GROUPBY(INFO.VIEW.RELATIONSHIPS(), [ID], "RelName", MINX(CURRENTGROUP(), [Relationship])) /* ---------- 1. Calculation logic: DAX that runs at query time ---------- */ VAR _Calculations = UNION( SELECTCOLUMNS( NATURALINNERJOIN(_Tables, SELECTCOLUMNS(INFO.CALCULATIONGROUPS(), "TableID", [TableID], "ModifiedTime", [ModifiedTime])), "Object Type", "Calculation Group", "Name", [TableName], "Last Modified", [ModifiedTime], "Expression", "" ), SELECTCOLUMNS(INFO.CALCULATIONITEMS(), "Object Type", "Calculation Item", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", [Expression]), SELECTCOLUMNS(INFO.MEASURES(), "Object Type", "Measure", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", [Expression]), SELECTCOLUMNS( NATURALINNERJOIN(_FormatStringDefs, SELECTCOLUMNS(INFO.MEASURES(), "OwnerID", [ID], "Owner", "measure " & [Name])), "Object Type", "Dynamic Format String", "Name", "Format string of " & [Owner], "Last Modified", [ModifiedTime], "Expression", [Expression] ), SELECTCOLUMNS( NATURALINNERJOIN(_FormatStringDefs, SELECTCOLUMNS(INFO.CALCULATIONITEMS(), "OwnerID", [ID], "Owner", "calculation item " & [Name])), "Object Type", "Dynamic Format String", "Name", "Format string of " & [Owner], "Last Modified", [ModifiedTime], "Expression", [Expression] ), SELECTCOLUMNS( NATURALINNERJOIN(_DetailRowsDefs, SELECTCOLUMNS(INFO.MEASURES(), "OwnerID", [ID], "Owner", "measure " & [Name])), "Object Type", "Detail Rows Expression", "Name", "Detail rows of " & [Owner], "Last Modified", [ModifiedTime], "Expression", [Expression] ), SELECTCOLUMNS( NATURALINNERJOIN(_DetailRowsDefs, SELECTCOLUMNS(INFO.TABLES(), "OwnerID", [ID], "Owner", "table " & [Name])), "Object Type", "Detail Rows Expression", "Name", "Detail rows of " & [Owner], "Last Modified", [ModifiedTime], "Expression", [Expression] ) ) /* ---------- 2. Model structure: what the engine has to scan and join ---------- */ VAR _Structure = UNION( SELECTCOLUMNS(INFO.MODEL(), "Object Type", "Model", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", ""), SELECTCOLUMNS( NATURALINNERJOIN( SELECTCOLUMNS(INFO.TABLES(), "ID", [ID], "ModifiedTime", [ModifiedTime]), SELECTCOLUMNS(INFO.VIEW.TABLES(), "ID", [ID], "TableName", [Name], "Expression", [Expression]) ), "Object Type", IF([Expression] = "", "Table", "Calculated Table"), "Name", [TableName], "Last Modified", [ModifiedTime], "Expression", [Expression] ), SELECTCOLUMNS( NATURALINNERJOIN( SELECTCOLUMNS(INFO.COLUMNS(), "ID", [ID], "ModifiedTime", [ModifiedTime], "Expression", [Expression]), SELECTCOLUMNS(FILTER(INFO.VIEW.COLUMNS(), [DataCategory] <> "RowNumber"), "ID", [ID], "ColumnName", "'" & [Table] & "'[" & [Name] & "]") ), "Object Type", IF([Expression] = "", "Column", "Calculated Column"), "Name", [ColumnName], "Last Modified", [ModifiedTime], "Expression", [Expression] ), SELECTCOLUMNS( NATURALINNERJOIN( SELECTCOLUMNS(INFO.RELATIONSHIPS(), "ID", [ID], "ModifiedTime", [ModifiedTime]), SELECTCOLUMNS(_RelNames, "ID", [ID], "RelName", [RelName]) ), "Object Type", "Relationship", "Name", [RelName], "Last Modified", [ModifiedTime], "Expression", "" ), SELECTCOLUMNS( NATURALINNERJOIN(_Tables, SELECTCOLUMNS(INFO.HIERARCHIES(), "TableID", [TableID], "HierarchyName", [Name], "ModifiedTime", [ModifiedTime])), "Object Type", "Hierarchy", "Name", [TableName] & "[" & [HierarchyName] & "]", "Last Modified", [ModifiedTime], "Expression", "" ) ) /* ---------- 3. Data loading: M queries, partitions, refresh ---------- */ VAR _DataLoading = UNION( SELECTCOLUMNS( NATURALINNERJOIN(_Tables, SELECTCOLUMNS(INFO.PARTITIONS(), "TableID", [TableID], "PartitionName", [Name], "ModifiedTime", [ModifiedTime], "QueryDefinition", [QueryDefinition])), "Object Type", "Partition", "Name", [TableName] & " / " & [PartitionName], "Last Modified", [ModifiedTime], "Expression", [QueryDefinition] ), SELECTCOLUMNS(INFO.EXPRESSIONS(), "Object Type", "M Expression / Parameter", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", [Expression]), SELECTCOLUMNS( NATURALINNERJOIN( SELECTCOLUMNS(INFO.TABLES(), "TableID", [ID], "TableName", [Name], "ModifiedTime", [ModifiedTime]), SELECTCOLUMNS(INFO.REFRESHPOLICIES(), "TableID", [TableID], "SourceExpression", [SourceExpression]) ), "Object Type", "Refresh Policy", "Name", [TableName], "Last Modified", [ModifiedTime], "Expression", [SourceExpression] ) ) /* ---------- 4. Security and perspectives ---------- */ VAR _SecurityAndPerspectives = UNION( SELECTCOLUMNS(INFO.ROLES(), "Object Type", "Role", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", ""), SELECTCOLUMNS( NATURALINNERJOIN(_Tables, NATURALINNERJOIN(_Roles, SELECTCOLUMNS(INFO.TABLEPERMISSIONS(), "RoleID", [RoleID], "TableID", [TableID], "ModifiedTime", [ModifiedTime], "FilterExpression", [FilterExpression]))), "Object Type", "RLS Filter", "Name", [RoleName] & " / " & [TableName], "Last Modified", [ModifiedTime], "Expression", [FilterExpression] ), SELECTCOLUMNS(INFO.PERSPECTIVES(), "Object Type", "Perspective", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", ""), SELECTCOLUMNS( NATURALINNERJOIN(_Tables, NATURALINNERJOIN(_Perspectives, SELECTCOLUMNS(INFO.PERSPECTIVETABLES(), "PerspectiveID", [PerspectiveID], "TableID", [TableID], "ModifiedTime", [ModifiedTime]))), "Object Type", "Perspective Table", "Name", [PerspectiveName] & " / " & [TableName], "Last Modified", [ModifiedTime], "Expression", "" ) ) /* ---------- 5. OPTIONAL: newer compatibility levels only ---------- UDFs and hybrid-table data coverage definitions don't exist on older models, and referencing them fails the WHOLE query at compile time with "references an object or property that is unavailable in the current edition of the server". If you see that error, comment out this VAR and remove _NewerFeatures from the final UNION below. */ VAR _NewerFeatures = UNION( SELECTCOLUMNS(INFO.USERDEFINEDFUNCTIONS(), "Object Type", "UDF", "Name", [Name], "Last Modified", [ModifiedTime], "Expression", [Expression]), SELECTCOLUMNS( NATURALINNERJOIN( SELECTCOLUMNS(INFO.PARTITIONS(), "PartitionID", [ID], "PartitionName", [Name]), SELECTCOLUMNS(INFO.DATACOVERAGEDEFINITIONS(), "PartitionID", [PartitionID], "ModifiedTime", [ModifiedTime], "Expression", [Expression]) ), "Object Type", "Data Coverage Definition", "Name", [PartitionName], "Last Modified", [ModifiedTime], "Expression", [Expression] ) )EVALUATEFILTER( UNION(_Calculations, _Structure, _DataLoading, _SecurityAndPerspectives , _NewerFeatures ), [Last Modified] > _ChangedSince)ORDER BY [Last Modified] DESC
The part of the query that checks for changes in newer features, such as DAX UDFs, may error on older models with lower compatibility levels so you may need to comment that, and the part of the UNION that references it, out. Instructions on how to do this are in the comments in the query.
It’s also worth pointing out that sometimes the modification dates change when you don’t expect them to. For example I saw that when I added a calculation group to my semantic model all the modification dates of my measures were changed too, probably because adding a semantic model in Desktop changes the discourage implicit measures property on the model automatically. But overall it seems to behave sensibly enough for it to be useful so give it a try and let me know what you think. I can also imagine it being useful to combine this information with the list of measures, columns etc that are touched by a given DAX query (something I wrote about here) so you can see whether any objects that the query uses have changed recently.