cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
etip
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
V-pazhen-msft
Community Support
Community Support

@etip 


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.

View solution in original post

1 REPLY 1
V-pazhen-msft
Community Support
Community Support

@etip 


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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors