I wanted to dive into the topic of supporting tables inside of Power BI in this particular blog.
Supporting tables are a unique development technique and are used for supporting logic that you might run through your core data model. Think of your core data model as the look up and fact tables that come from your raw data.
Supporting tables enable unique types of analysis like dynamic grouping or segmenting of your data based on custom logic in your formulas.
What I will work through in a couple of these examples below, is how you can set up your model to manage these supporting tables. Not only to manage one but manage multiples of them. This will ultimately allow for a much wider range of analysis to your more common insights.
The first example that I cover here is secondary table logic. What we are going to do is create a simple table that has no relationship to the rest of our model. We're going to run logic through that particular secondary table to then group the results from a dimension so that we can visualise those groups within our Power BI report.
This is detailed quite effectively in this tutorial so I highly recommend working through it to really understand what you can do with these secondary tables.
In this next example, we're going to work through a threshold triggers scenario.
This can be a very relevant scenario in a banking or insurance environment where you would have a range of different triggers that you could potentially need to work your data through to see if there is a particular group a customer would land in (from a risk perspective as an example).
You need to set up an advanced DAX formula combination and then work through your supporting table to see if any triggers or thresholds are being met.
I dive into a number of advanced formula concepts you need to get right inside of your Power BI models to be able to showcase these insights effectively.
These are two quite advanced examples of what you can do inside of Power BI that will also show you the possibilities around advanced analysis.
Hopefully to can see that you can produce some highly effective insights using these techniques in Power BI