A table of multiple values was supplied

[Anonymous]

Hi All,

Please help me with the error shown below:

What I am trying to do here is to create an intersection between Week 47 & 48 to calculate returning customers in Week 48. And then show Profit, Revenue for those returning customers in Week 48 

This file can be downloaded from here: https://drive.google.com/file/d/19CxgnRrhgFXwubApt221ot3HnDXnfKa0/view?usp=sharing

Would be good if you can explain where i am going wrong.
By searching online i realized i may need to use FIRSTNONBLANK or LASTNONBLANK

[Anonymous]

@Anonymous Your concept clarity is simply mid blowing.
I will be honest- I have understood it mostly, not 100% sure why you were prescient to have used ALL in the first place itself. Anyways, I used ALL for the Week 47 condiion alone, it works, so that it reminds me why it was needed and of your valuable input here.
Do you have your YouTube channel? I have few queries that went unaswered, mind if if tag you there?

[Anonymous]

I don't have a channel. I just don't have time for this. I'm a very, very busy man 🙂 (contrary to what you might think because of me helping people here).

[Anonymous]

@Anonymous 

OK, it's not that hard to see why your code didn't work with CALCULATETABLE. Simple reason being that when you put CALCULATETABLE at the top, the filtering condition (the 2nd argument of the function) applies to EVERYTHING inside the first argument of CALCULATETABLE. Hence, one of your arguments under INTERSECT returns an empty set due to filtering that affects 'Sales by Store' under FILTER. To counteract this you can filter the table wrapped in ALL, which will then ignore all filters on the (expanded) table 'Sales by Store' and thus the final formulas will return what you need.

By the way, my code is free from this defect because when I filter I always use ALL, so you can wrap my whole code in CALCULATETABLE and it'll still return what you need.

[Anonymous]

@Anonymous , @HotChilli - Can't thank you both enough for definitive directions. 
I am finishing up an advanced DAX course, will buy this book then- because the video course is very costly $350.

I am yet to absorb your suggestions on code here, will get back if i need further help or else would accept the solution in 1/2 days. Sincere Thanks 🤗

[HotChilli]

I accept your explanation.  The forum is a great place to learn and there are some really expert people on here who give their time to help.

Regarding the filter,  a FILTER always creates a new table (in memory in this case) with the filtered rows.  FILTER is an iterator. The 2nd argument has to be boolean to check each row. Incidentally this is why FILTERing a large fact table has to be done with care.

In the example you gave, DATESBETWEEN returns a table which is why the error is thrown.

You can use the DATESBETWEEN code but you will have to rewrite to make the 2nd argument of FILTER  a boolean.  Alternatively, rewrite the 2nd argument to test each row for the correct dates (hint : you can combine logical tests with && )

[Anonymous · Accepted solution]

Table =
var WK47_StartDate = DATE( 2018, 11, 18 )
var WK47_EndDate = DATE( 2018, 11, 24 )
var WK48_StartDate = DATE( 2018, 11, 25 )
var WK48_EndDate = DATE( 2018, 12, 01 )
var CustomersInWK47 =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                ALL(
                    'Sales by Store'[Transaction_Date],
                    'Sales by Store'[customer_id]
                ),
                var CurrentTransDate =
                    'Sales by Store'[Transaction_Date]
                var TransDateInWK47 =
                    and (
                        WK47_StartDate <= CurrentTransDate,
                        CurrentTransDate <= WK47_EndDate
                    )
                return
                    TransDateInWK47
            )
            "@Customer",
                'Sales by Store'[customer_id]
        )
    )
var CustomersInWK48 =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                ALL(
                    'Sales by Store'[Transaction_Date],
                    'Sales by Store'[customer_id]
                ),
                var CurrentTransDate =
                    'Sales by Store'[Transaction_Date]
                var TransDateInWK48 =
                    and (
                        WK48_StartDate <= CurrentTransDate,
                        CurrentTransDate <= WK48_EndDate
                    )
                return
                    TransDateInWK48
            )
        ),
        "@Customer",
            'Sales by Store'[customer_id]
    )
var CustomersInBothWeeks =
    INTERSECT(
        CustomersInWK47,
        CustomersInWK48
    )
return
ADDCOLUMNS(
    CustomersInBothWeeks,
    "profit", CALCULATE( [Profit], 'Calendar'[Week_ID] = 48 ),
    "Revenue", CALCULATE( [Customer Sales], 'Calendar'[Week_ID] = 48 )
)

This code could be written more succintly using the GENERATE function...

[Anonymous]

@Anonymous Thanks a bunch. Your code worked- I had to tweak it at 2 places, highlighted in green box below
1) SELECTCOLUMNS syntax for name & expression parameters would come before comma to complete arguments

2) CALCULATETABLE would be used in the end to return values of profit & revenue for customers just for the week 48

Without the 2nd change in your code, i.e. for restricting the results to Week No 48- It behaved just like my code below did:

Calculated Table Joins Assignment_Self = 
CALCULATETABLE(
    ADDCOLUMNS(
        INTERSECT(
            DISTINCT(
                SELECTCOLUMNS(
                    FILTER(
                    'Sales by Store',
                    'Sales by Store'[transaction_date] IN DATESBETWEEN('Calendar'[Transaction_Date], DATE(2018,11,18), DATE(2018,11,24))
                    ),
                    "Customer",
                    'Sales by Store'[customer_id]
                )
            ),
            DISTINCT(
                SELECTCOLUMNS(
                    FILTER(
                    'Sales by Store',
                    'Sales by Store'[transaction_date] IN DATESBETWEEN('Calendar'[Transaction_Date], DATE(2018,11,25), DATE(2018,12,01))
                    ),
                    "Customer",
                    'Sales by Store'[customer_id]
                )
            )
        ),
        "Profit",
        [Profit],
        "Revenue",
        [Customer Sales]
    ),
    'Calendar'[Week_ID] = 48 
)

But your code worked finally with my CALCULATETABLE, mine didn't- for me it just shows no rows after i use CALCULATETABLE thing. Before this everything in my code worked exactly as yours did before CALCULATETABLE. Mind having a glance?
The file is here- https://drive.google.com/file/d/1-nQFVF_RMUOefKyF4O3KjzM_h0zW8Ezl/view?usp=sharing

[Anonymous]

You don't need CALCULATETABLE at all (if you don't want to use it). You can put the filter on Week_ID right into the measures:

caclulate(

    [Profit],

    <your filter here>

)

and same with [Customer Sales].

The first mistake in my code was due to the fact that I was wrting the code without a model and was shuffling the code around, then forgot to put the piece in the right place. This happens from time to time when I write code without any model.

PS. I've made changes to my original code so that it's now correct and doing exactly what you wanted.

[Anonymous]

@Anonymous You are a genius. Even my code tweaked similarly worked as expected, I know Variable way as you showed is better performance wise- am hoping to get used to that style soon

But, if you have few mins to spare- I would like to know why your method worked with CALCULATETABLE as well, whereas mine didn't. I attached my code with CALCULATETABLE in my last message and it's also there in the file link there.

[Anonymous]

@HotChilli Thanks for pointing me in the right direction. With the Boolean thing you suggested- does it mean that despite using FILTER, it actually does not filter the table but just checks it for all row values? I am trying to research more on what you may be trying to convey here. If you find time to extrapolate more on what you are trying to suggest, that would be helpful.

On a side note, I am not sure why the forum can't be used for any course related queries? I am not completing my assignment seeking help here- I have trainer's video for that. I am exploring ways to approach problems for evolving and clarifying my understanding.

[Anonymous]

@Anonymous 

If you really, really, really want to understand DAX... well, then you've got 2 choices:

1) "The Definitive Guide to DAX" by Marco Russo and Alberto Ferrari (requires at least 4 re-reads).

2) "Mastering DAX" - a course by the same people on www.sqlbi.com (paid but there's nothing better than this in the whole wide world).

Yes, these two (and a bit of practice) are enough to turn you into a DAX ninja. I know what I'm saying.

[HotChilli]

I don't think this forum is the right place to get answers for DAX course assignments......

Let me give you a pointer in the right direction.  The 2nd argument in a FILTER is "A boolean (True/False) expression that is to be evaluated for each row of the table."  The important part is that it is a boolean.

[Anonymous]

@amitchandak Thanks for the suggestion. 
So this comes from an assignment on a DAX course that I am doing.

The trainer of the course used CALCULATETABLE as well but hadn't introduced the function in the course till that time. 
Hence, I used SELECTCOLUMNS & FILTER, would you know how to make this work using these functions as well 🙂

The link to file is also given in my original question. Please suggest the way using SELECTCOLUMNS & FILTER or the reason as to why this wouldn't work ever

[amitchandak]

@Anonymous , Try like

Table = 
    ADDCOLUMNS(INTERSECT(
        SELECTCOLUMNS(
            CALCULATETABLE(
            'Sales by Store',
            DATESBETWEEN('Calendar'[Transaction_Date], DATE(2018,11,18), DATE(2018,11,24))
            ),
            "Customer",
            'Sales by Store'[customer_id]
        ),
        SELECTCOLUMNS(CALCULATETABLE(
            'Sales by Store',
            DATESBETWEEN('Calendar'[Transaction_Date], DATE(2018,11,25), DATE(2018,12,01))
            ), 
            "Customer",
            'Sales by Store'[customer_id]
            )
    ) ,"profit",[Profit], "Revenue",[Customer Sales])