Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

@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.

View solution in original post

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

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors