# Virtual Tables learning

Hi,
I`ve watched a lot of videos but I have yet to master the DAX virtual table knowledge. Currently, I am working to improve my skills in this matter.

First, is there an article that comprehensively explains all virtual table functions? I could not find that.

Second, please see the attached file. I do not understand how I can make multiple levels of a unique value virtual table.

I want to make a two-level unique value table: ‘Customer-Lookup’[customer_id]) (‘Product-Lookup’[product_id]. Currently, I only know how to make that that using the below formula. Is there a better, more efficient way of doing this?

Made Table 2 =
FILTER(
CROSSJOIN(VALUES(‘Customer-Lookup’[customer_id]),VALUES(‘Product-Lookup’[product_id])),
“Sales”, [Sales]),
[Sales]>0)

Questions 123.pbix (2.8 MB)

Thank you!

@Roboboboberts I am going to give a general advice. For measures use FILTER before ADDCOLUMNS to eliminate non existing combinations as early as possible

``````EVALUATE
FILTER (
CROSSJOIN (
VALUES ( 'Customer-Lookup'[customer_id] ),
VALUES ( 'Product-Lookup'[product_id] )
),
[Sales] <> 0
),
"Sales", [Sales]
)
``````

and for Calculated tables use SUMMARIZECOLUMNS

``````EVALUATE
SUMMARIZECOLUMNS (
'Customer-Lookup'[customer_id],
'Product-Lookup'[product_id],
"Sales", [Sales]
)
``````

Your version put extreme load on Formula Engine by materializing 16 million rows and then does the filtering and runs in ~8 seconds

Filtering before hand materializes the right amount of rows and runs in ~1.2 seconds

And SUMMARIZECOLUMNS is already the optimized function so no surprises it runs in ~700 ms

3 Likes

Hi @AntrikshSharma, that`s a brilliant answer, sadly I now have two more questions.
For example, if I wanted to run a formula on the virtual table I wrote previously. How do you exactly I do that?
For example, for the previously mentioned virtual table, how do I count unique customer ids?

EDIT: I am only aware of using the X functions to do this.
EDIT2: For example, a measure that calculates the number of customers with purchases using a virtual table.

@Roboboboberts You can use something like this:

``````Measure =
COUNTROWS (
DISTINCT (
SELECTCOLUMNS (
FILTER (
CROSSJOIN (
VALUES ( 'Customer-Lookup'[customer_id] ),
VALUES ( 'Product-Lookup'[product_id] )
),
[Sales] <> 0
),
"Sales", [Sales]
),
"Customer[Customer ID]",
'Customer-Lookup'[customer_id]
)
)
)
``````

If the goal is to count customers with purchase then the above formula is an overkill, you should use something like this:

``````Measure 2 =
COUNTROWS (
SUMMARIZE (
'FoodMart-Transactions-1997-1998',
'Customer-Lookup'[customer_id]
)
)``````
1 Like

@AntrikshSharma

You have already answered everything exceptionally but I guess I have my last thing.

Is there a way I can insert these filter in the below formula?
For example, [Year] is 1997 and/or if the sales are greater than 10k per customer.

COUNTROWS (
SUMMARIZE (
‘FoodMart-Transactions-1997-1998’,
‘Customer-Lookup’[customer_id]
)
)

One way to do it is

SUMMARIZECOLUMNS (
DIM_Customer[customer_id],
Dim_Product[product_brand],
“Sales”, IF([Sales]>100,[Sales],BLANK()))

But how do I efficiently filter customer id ?