Creating Virtual Tables in Power BI Using DAX

In this session, I want to show you how to use DAX / M expressions to create virtual tables. You may want to do this to create an aggregated view of your data to then work with, or perhaps filter down a dataset. I’ll show you examples of both, including doing both together.

Once you’ve created a virtual table, you can then work with it in much the same way as any other table, creating derived fields and even relationships with other tables.

I’m using a very basic dataset, showing payments made against invoices by customers, which I’ve loaded into PowerBI.

As you can see, a customer may make more than one payment towards a given invoice.

Aggregated Table

What I want to do is create a table showing the total payments made towards each invoice – this is so that I can then create a relationship with the invoice data which tells me how much is actually due, allowing me to then review which invoices are outstanding.

To do this, within the main PowerBI environment, goto the Data Modelling tab, and select New Table

This will activate the Dax Formula Editor.

To create the table, we use the SUMMARIZE function.

AggregatedPayments = SUMMARIZE (Payments,Payments[Customer],'Payments'[Invoice],"TotalPaidToInvoice",SUM ( 'Payments'[AmountPaid] ))

The function is firsts asks for a source table (Payments)

It then asks for the field, or fields (you can have as many as you need) you want to group, then for each field you want to aggregate, the field name (TotalPaidToInvoice) and the expression (SUM(‘Payments'[AmountPaid]))

If you’re familiar with SQL, is can be broadly thought of as

SELECT ([FROM],[GROUPBY],[FIELDS TO AGGREGATE])

Use the tick to validate your expression

You will now have a new ‘table’ to work from

Filtered Table

Rather than aggregating a table, you may want to filter it instead, to perhaps works with a cleansed subset of data (without discarding the filtered out data altogether). In my example, I want to filter out negative payments (reversals), to just see inbound payments

We can use the same approach, but use the CALCULATETABLE function

Select ‘New Table’ from the modelling tab as before.

In the formula bar, enter

FilteredPayments = CALCULATETABLE ('Payments',Payments[AmountPaid] > 0)

This will create you a filtered table, which again, you can work with as though it’s any other table

Filtered, aggregated table

Naturally, you can combine these is any way you see fit. So you have created an aggregated table from a filtered dataset.

AggregatedAndFiltered = SUMMARIZE (CALCULATETABLE ('Payments',Payments[AmountPaid] > 0),Payments[Customer],'Payments'[Invoice],"TotalPaidToInvoice",SUM ( 'Payments'[AmountPaid] ))

Summary

Whilst this is a pretty simplistic example, hopefully it will give you some ideas for future application. I used this approached where I had a large dataset which I needed to join to itself (as I had parent & child transactions within the data) – which PowerBI doesn’t support. By creating virtual tables of the data, I was then able to create the relationship between the two filtered sets, thereby achieving the desired outcome.

You can download the example PowerBI file from here