Welcome to the Iamdata Solutions Asset Management Newsletter - Special Halloween post 30 October 2023
Virtual tables are an important concept in Power BI that allows you to create temporary tables on the fly. They are created using DAX expressions and can be used in various ways to enhance your data analysis.
Virtual tables are created by using the SUMMARIZE or GROUPBY functions, which takes one or more input tables and group the data according to specified columns. The resulting table is a virtual table that can be used in other DAX expressions, such as measures or calculated columns.
Here are some of the ways you can use virtual tables in Power BI
Power BI Virtual Table - SUMMARIZE and FILTER
You can use virtual tables to filter your data based on specific criteria. For this example, I will create a virtual table that shows the total measurement of the different Asset Classes, but only for assets with a condition rating greater than 4 (bad condition).
I will use the SUMMARIZE function along with the FILTER function:
DAX: TotalValuationMeasurementByClass =
SUMMARIZE(
FILTER('Transport Assets', 'Transport Assets'[Condition] > 4), -- Set the condition rating threshold here (e.g., > 4).
'Transport Assets'[Class],
"Total Valuation Measurement", SUM('Transport Assets'[Measurement])
Power BI Virtual Table - Joining Two Tables
Let's create a Power BI virtual table example that demonstrates joining two tables using the SUMMARIZE function. For this example, I have used two tables: (Transport Assets table and Capital Works Program table).
DAX:
TotalRepairsAndMeasurementByDepartmentAndMonth =
SUMMARIZE(
'Transport Assets',
'Transport Assets'[Class],
'CapitalMaintenanceCostsOver12Months'[Department],
'CapitalMaintenanceCostsOver12Months'[Month],
"Total Capital Cost", SUM('Transport Capital Works Program'[Cost]),
"Measurement", SUM('Transport Assets'[Measurement])
We can now use this virtual table to create various visualisations and reports in Power BI to analyse the relationship between capital works cost, road area, and year.
Power BI Virtual Table - Time Intelligence
You can use virtual tables to create time intelligence calculations, such as year-to-date or rolling averages. For this example, I have used the DATESYTD function to create a virtual table that shows the year-to-date maintenance costs for each asset class and year.
DAX:
Year-to-Date Maintenance Costs by Asset Class and Year =
SUMMARIZE(
FILTER(
ALL('Transport Assets'),
'Transport Assets'[Date] <= MAX('Transport Assets'[Date])
),
'Transport Assets'[Class],
'Transport Assets'[Year],
"Year-to-Date Costs",
CALCULATE(
SUM('Transport Assets'[Cost]),
DATESYTD('Transport Assets'[Date]),
ALL('Transport Assets')
)
)
Why would you need a virtual table in your Power BI Report?
Virtual tables can be very powerful in Power BI, as they allow you to create complex calculations and views of your data without having to modify the underlying data model.
However, it's important to note that they can be resource-intensive, so it's crucial to use them judiciously and optimise their performance as much as possible.
Despite their potential resource intensity, virtual tables in Power BI need not be too scary 😱 as long as you use them wisely and for justified purposes! 🧙🏽
I have worked on many different projects with my Local Government clients, from designing and developing Power BI Reports, to building SQL Server databases for spatial data, to managing and maintaining the GIS and the Asset Management systems. If you'd like to discuss how we might work together, then please email Jill at ➡️ jill.singleton@iamdata.solutions
If you would like to receive the latest Newsletter Blog straight to your inbox, please subscribe here: ➡️ https://www.iamdata.solutions/subscribe
You can read all our Newsletters and Blogs here:➡️ https://www.iamdata.solutions/blog
You may also be interested in our Projects Page:➡️ https://www.iamdata.solutions/past-projects
Check out what our clients say about us here:➡️ https://www.iamdata.solutions/reviews
If you would like to see a particular topic covered in these newsletters, then please let me know about it. The chances are other people will be interested and would like to hear about it too! Please email me at: ➡️ jill.singleton@iamdata.solutions with your suggestions.