When using Power BI you can sometimes think you are limited to the data sitting within your tables. Well with Power BI that it certainly not the case. You have enormous flexibility to build on top of your data by bringing in values from additional tables to flex the numbers or stress your results with scenarios.
Now at the time of recording the below video the GENERATESERIES/SELECTEDVALUE functions within Power BI did not exist, so this was the old way you had to create simple tables or columns of information that could be used in slicers, but this is still a great technique to learn and adopt in other situations.
What I’ve achieved here is to showcase how you can create a table on the fly, then place a column of that table into a slicer to then extract or ‘harvest’ a value inside a measure to then be inserted into an existing calculation or another measure that you have in your model.
There are just so many applications for this in Power BI and that is why I call this type of analytical work as building on top of your model, as you start with the core data that you might extract from your database but then bring in all these additional values to adjust and shock results to see what may or may not occur within your results.
These techniques are how you really take your analysis within Power BI to the next level as you have almost unlimited ways you evaluate insights from your initial key metrics.
Check out the video below for more details on this technique (remember similar things can be acheived now with the GENERATESERIES/SELECTEDFUNCTIONS function and also the ‘What If’ parameter feature)