A single value for column xxx in table yyy cannot be determined.

[Anonymous]

Hey there,

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 begin 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 begin date of a given week :

= CALCULATE (

SUMX(Daily_Stock;[Price]*RELATED(Stock_list[Qty]) );

FILTER (Daily_Stock;

Daily_Stock[Date] =weeklies[begin_date] )

)

I works fine but i would like to exclude some stocks which were sold before the begin date (i have other reasons to be able to achieve this) so 'im 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

[Anonymous · Accepted solution]

@Anonymous 

The problem is related() function only work to refer to a 1 side table, I guess Stock_list[sold_date] column is a non-unique (repeated date). DAX RELATED TABLE FUNCTIONS - Data Bear - Power BI Training and Consulting.

If the weeklies[begin date] column is the 1 side table (unique date), you can replace the if function to the following expression. Otherwise, you need to use RELATEDTABLE function (DAX) - DAX | Microsoft Docs to compare.

if(Stock_list[sold_date]  < related(weeklies[begin date]) ; 0 ;1)

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.