Virtual Tables learning

DAX DAX Calculations
1

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(
ADDCOLUMNS(
CROSSJOIN(VALUES(‘Customer-Lookup’[customer_id]),VALUES(‘Product-Lookup’[product_id])),
“Sales”, [Sales]),
[Sales]>0)

Questions 123.pbix (2.8 MB)

Thank you!

2

@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
ADDCOLUMNS (
    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

image
image1376×582 79.6 KB

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

image
image1280×389 47.6 KB

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

image
image1386×138 15.4 KB

3 Likes
3

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.

4

@Roboboboberts You can use something like this:

Measure = 
COUNTROWS ( 
    DISTINCT ( 
        SELECTCOLUMNS ( 
            ADDCOLUMNS (
                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
5

@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 ?