Introduction
Once we finish and deploy a Power BI report we have built up a trove of knowledge.
We know which parts of our data model are used where and how our DAX measures are defined. Over time this know-how will deteriorate and for complex reports it is a struggle from the beginning how exactly everything is functioning.
So let us assume we have either a complex report or take a look at something we have not touched for a while.
How can we find which artifacts (columns and measures) are needed to sustain or to build a certain visual or even a whole page of our report?
This can get very complex since we have to take into the following:
-Measures and column dependencies (measure X references measure Y which sums up column Z and has a filter on table XY)
-Power Query (A column could be used in a join there)
-Visuals (something that is in an axis or values field - but it could also be a tooltip or else)
-Filters (either applied on the visual or on the page-level)
-Conditional formatting (this is probably the hardest to figure out because there are so many possibilities where to use it - think about font color or background but also dynamic titles etc.)
-Relationships (active or inactive which could be activated through DAX)
-Key columns, column sorting and other things that are model specific
Manual approaches
Option 1
The easiest way to test if something is used is obviously just deleting it and seeing what happens. This might not be a good test in a lot of cases like conditional formatting and it is a very slow way of testing.
Option 2
Alternatively I can also go through the visual and write everything down or build some kind of script using the .pbix or .pbip files that Power BI exposes.
Automated checks
As of July 2024, the only tool I know of that can tell you what is used in a visual or on a certain page is Measure Killer's "What If Analysis".
If I download, install and run Measure Killer, then analyze one of my reports and start the "What If Analysis"
Now I will see the window below. Let us first choose "visual" since we want to see everything needed to build one of the visuals in the report.
I will now get a huge list of visuals since I seem to have 69 visuals in my report.
Let us take a look at one of them to see the output
As we can see above, all the columns listed are one way or another used to "keep this visual alive". This can be directly used or indirectly. Let me explain what that means.
Calendar Year and Month Name (renamed to Month) are used directly in the visual in the X-axis.
dCalendar[Month] however is used to sort dCalendar [Month Name] as shown in the hierarchy above. That is already a column that will be harder to find if we do things manually.
Now what about the remaining columns, Dates, CustomerKey (dim table), OrderDate and CustomerKey (fact table)?
Let us look at them one by one:
At first glance dCalendar[Dates] does not seem to be used at all until we open the Measures (2) part.
Our "Customers (New)" measure is using columns, tables and measures itself. It is also using the [Dates] column. We need to take a look inside the DAX expression to find out.
We will now be able to see the expresssion used to build this measure. You can see that we actually have two references to the dCalendar[Dates] column.
This measure also references CustomerKey (dim table).
Next we might want to verify the remaining two columns used, OrderDate and CustomerKey (fact table)
OrderDate is actually used in our calendar table somehow. To verify this we can right click on dCalendar to get its M expression.
As you can see above, there is a List.Max operation in Power Query to get the latest OrderDate. This is how our calendar table is built.
The last one on our list is CustomerKey from our fact table. This one is referenced in the second measures we are using inside the visual (Customers (Active))
To make sure this is accurate, we can check the DAX expression again by right clicking on the measure and selecting "View DAX expression".
This was an easy one!
As you have learned, it is very complex to find all columns and measures used even in one visual of our report.
Now let us take a look at a page.
For that I need to go back to the selection windows of Measure Killer's "What If Analysis" and instead of "visual" -> choose "page".
I will not go into detail like I did above to go through every single usage but rather just take the output for granted and assume it is correct.
Remember that all columns listed here are one way or another used on that page, they could be used somehow in the model, Power Query or via a DAX expression etc. See the introduction on top of this post for more.
In the end you can check everything right here and go through the artifacts needed to build this page or to keep it alive as I like to say.
Comentários