CODEWITHSUNDEEP
daxpowerquery
Sebastien Chemouny
Sebastien Chemouny

Reputation: 197

A single value for column .... cannot be determined

I have 2 tables for stock management. 1 for the list of stock and some other properties and 1 for the daily values (i have a relationship between both on the index of the stock).

I would like to have a weekly performance ie the value has increased/decreased by xx from the previous week.

So I created a table (weeklies) with a few rows which correspond to a week for each row. I have 2 columns: 1 is the beginning date of the week, 1 is the last date of the week.

Im creating a calculated third column with the sum of all the values at the beginning date of a given week :

CALCULATE (
    SUMX ( Daily_Stock; [Price] * RELATED ( Stock_list[Qty] ) );
    FILTER ( Daily_Stock; Daily_Stock[Date] = weeklies[begin_date] )
)

It works fine but I would like to exclude some stocks which were sold before the beginning date (i have other reasons to be able to achieve this) so I'm trying to multiply by 0 if it is the case for that specific stock.

CALCULATE (
    SUMX (
        Daily_Stock;
        [Price] * RELATED ( Stock_list[Qty] )
            * IF ( RELATED ( Stock_list[sold_date] ) < weeklies[begin date]; 0; 1 )
    );
    FILTER ( Daily_Stock; Daily_Stock[Date] = weeklies[begin_date] )
)

There I have the following error :

A single value for column sold_date in table Stock_list cannot be determined.

Tweaking around a little bit and I had the same error on the weeklies table.

Does anyone know what I should be doing here?

I can explain more, I wanted to avoid a too-long post.

thanks

Upvotes: 0

Views: 3840

Answers (2)

Ankita Kumar
Ankita Kumar

Reputation: 1

This error occurs because you are creating a new measure, when you should be creating a new column instead. The above result is different for each record and that is why you should be creating a new column where the result is different for each record. Hope this helps!

Upvotes: 0

Michael Christensen
Michael Christensen

Reputation: 44

I think the issue is the relation.

I assume the RELATED is based on the stock index you mentioned.

I think related stock_list[sold_date] returns all dates that RELATED stockID has ever been sold.

Which would mean you are trying to compare more than one date with weeklies[begin date].

image copied from powerpivotpro on using VALUES with IF in measures. enter image description here

If i am right, you need another way of relating to your stocklist to get singular matches. I am not sure if the VALUES solution rob collie uses for measures will work here, but maybe it is worth testing. Rob collie powerpivotpro - Magic of IF(VALUES)

Upvotes: 2

Related Questions