Newbie trying to get started

May 22, 2016 at 10:53 AM
Hi. I've been able to connect to my Excel 2016 model and shows me on the left all my measures.

I was hoping to use this to show me what table i am creating in my measure as getting errors saying I have supplied a table of multiple values when one expected. Is this the sort of thing I can do with this to try and work out where i'm going wrong? If I drop the measure on the pane and hit run i just get "the result returned by the server is not a row set" which is not helpful.

The code is
DEFINE 
MEASURE PPC[profit] = CALCULATE(
                                    VALUES(PPC[GPTV]),
                                    FILTER(
                                               ALL(PPC),
                                               PPC[Month]=VALUES(DateKey[Month])  &&
                                               PPC[Depot]=VALUES(Depot[Depot])
                                               )
                                    )
I just want to see what the result of the FILTER bit is as clearly its wrong. can I do that?
Thanks for any advice to get me started

Mike
Coordinator
May 25, 2016 at 12:31 AM
So there are 2 issues here.

First is that you can't execute a DEFINE statement on it's own, it does not return anything. Which is why you get the error about the result not being a rowset. You could fix this by adding an EVALUATE statement. To return a single value that calculates the grand total for profit you could use the ROW function.

eg.
DEFINE 
MEASURE PPC[profit] = CALCULATE(
                                    VALUES(PPC[GPTV]),
                                    FILTER(
                                               ALL(PPC),
                                               PPC[Month]=VALUES(DateKey[Month])  &&
                                               PPC[Depot]=VALUES(Depot[Depot])
                                               )
                                    )
EVALUATE 
ROW("Profit", PPC[profit] )
If you wanted to see the profit split out by another column, like the month for instance you could use a pattern like the following with the ADDCOLUMNS function
DEFINE 
MEASURE PPC[profit] = CALCULATE(
                                    VALUES(PPC[GPTV]),
                                    FILTER(
                                               ALL(PPC),
                                               PPC[Month]=VALUES(DateKey[Month])  &&
                                               PPC[Depot]=VALUES(Depot[Depot])
                                               )
                                    )
EVALUATE 
    ADDCOLUMNS( 
        VALUES('Date'[Month])
        , "Profit"
        , PPC[profit] 
    )
The second problem is the underlying issue with your calculation. With the above queries DAX Studio is going to return the same error about a table with multiple values being supplied. The problem here is that CALCULATE expects a function that returns a scalar value (ie. a single value) while the Values function returns a single column table. It gets a little bit grey here as in some circumstances VALUES() can return a scalar value because if you have a table function that returns a table with one column and one row the tabular engine will do an implied cast of that table to a scalar value, but usually if you are using that pattern you will "protect" the VALUES call with an if expression. eg. IF( HASONEVALUE( VALUES(PPC[GPTV]) ), ... , ... )

But most of the time if you are using CALCULATE the first expression should either refer to a measure or to an aggregate function like SUM, MAX, MIN, AVG, etc.

So I'm guessing that for a profit measure you'd want to see the SUM so you should use an expression like the following:
DEFINE 
MEASURE PPC[profit] = CALCULATE(
                                    SUM(PPC[GPTV]),
                                    FILTER(
                                               ALL(PPC),
                                               PPC[Month]=VALUES(DateKey[Month])  &&
                                               PPC[Depot]=VALUES(Depot[Depot])
                                               )
                                    )
EVALUATE 
ROW("Profit", PPC[profit] )
May 30, 2016 at 9:19 AM
Hi. sorry delay replying after you extensive answer. That's really helpful. I was trying to create a table with my filter that only had one value as the combination of month and depot is unique. So actually what i was trying to understand is what table have I created in my filter as clearly not a single value. I don't understand why it isn't a single value so thought i could fiddle around with e the statement until it was. So I guess what I really want to do is some kind of EVALUATE SUMMARIZE CALCULATETABLE expression to see how many rows and columns my FILTER is generating rather than the actual answer?

In this case my PPC table looks like this

Month Depot GPTV
Apr16 BAS 50
Apr16 LIV 75
MAY16 BAS 60
May16 LIV 90

So for the pivot with May16 and BAS on row and column there is only the unique value 60. So if my filter was correct I would have a single cell, but clearly I have more and not sure if I have one month and lots of depot or other way round. Or even all of both!

Thanks
Mike
Coordinator
May 30, 2016 at 9:33 PM
Actually, after spotting the obvious error with trying to use VALUES() in the first argument to CALCULATE I missed the fact that you have other VALUES calls inside the FILTER function. I'm not really sure what you are trying to do here. If you are trying simulate a relationship in code then there is an example of that in this article But I'm not entirely sure if that is what you are trying to do.

The problem here is not the detail level, it's the calculation of the grand totals. At the grand total level you will always have multiple values coming back from the two calls to VALUES() So doing an EVALUATE SUMMARIZE won't help here as this is probably more a case that the approach you've used does not cater for totals than issues in the data (although those could be there too)

Can you explain a bit more about what problem it is that you are trying to solve with this formula? And why you can't just do a straight sum of the [GPTV] column?
Jun 3, 2016 at 3:36 PM
Hi

Lets start at the beginning as clearly I've gone off the cliff somehwere.!!!

I am getting weekly data by depot of clicks on our website. I've created a measure that converts these clicks to bookings [Bookings Estimate] based on an average of conversion form the preceding month. The next step is to turn this in money. However i don't get a report of the value of transactions until the EOM so during the month i was just going to use an average value (GPTV=Gross profit ticket value) for each Depot. This is the table above where Apr16 are real numbers and May some made up numbers.

My pivot table has Depot as rows and a slicer for the month of May16. So what i am trying to create is a measure that odes the following:

[Bookings Estimate]*GPTV value from the table where the Depot = Depot on the row and Month = month in the slicer

So essentially I was trying to filter my table by the row and the month slicer to give a single unique value for each combination of depot/month in the pivot. i tried using various versions of CONTAINS without any more success.

Does that make more sense? i was hopnig to use DAX Studio to see the output of the fitler bit to see what table I was generating.
Thanks
Mike