cancel
Showing results for
Did you mean:
Frequent Visitor

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

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

1 ACCEPTED SOLUTION
Community Support

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.

Community Support

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.

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks