Forum Discussion
Creating a custom column over 2 columns from 2 different tables
Hello all,
I'm trying to make a custom column in one table with conditions over columns in that table and another table. However when I try to do that and give the tables name it says that it cant determin a single value for that colomn of the other table.
Mutatie graad Percentage =
if(Table1[Kavel ID] = Data_WBS_tbv_Beleidsportaal[Uniek nummer];
if(Data_WBS_tbv_Beleidsportaal[Kenmerknummer] = 2016007;
if(Data_WBS_tbv_Beleidsportaal[Tekstwaarde] = BLANK();
0 ;
Data_WBS_tbv_Beleidsportaal[Tekstwaarde]/100);
BLANK());
BLANK())
This is what I've been trying to do but i get this error with it:
A single value for column 'Tekstwaarde' in table 'Data_WBS_tbv_Beleidsportaal' cannot be determined.
This can happen when a measure formula refers to a column that contains many values without
specifying an aggregation such as min, max, count, or sum to get a single result.
I have to specify what i want to do with all of the values it gives even though i filtered double values out with the first if statement
(the column it get the data out (Tekstwaarde' in table 'Data_WBS_tbv_Beleidsportaal') does contain multiple value's but there either emputy or filtered out by the if statment so i do not know why it keeps giving me the error.
So my question, could anyone help me with fixing the error or help me with another method to make a custom calculated column over columns in 2 different tables?
greatings,
Joshmar Steffens
Hi Joshmarsteffens,
You can try to use below formula, I test with your sample file and it works on my side.
Check = var result= Lookupvalue(Records[Textuele Waarde],Records[Kavel ID],Kavel[Kavel ID ],Records[Kenmerk nummer],2016007) return IF(result<>blank(),result/100,0)
Reards,
Xiaoxin Sheng
9 Replies
Hi Joshmarsteffens,
Does these table contains the relationship? If as I said, you can use related function to invoke the related row content.
if(Table1[Kavel ID] = Related(Data_WBS_tbv_Beleidsportaal[Uniek nummer]); if(Related(Data_WBS_tbv_Beleidsportaal[Kenmerknummer]) = 2016007; if(Related(Data_WBS_tbv_Beleidsportaal[Tekstwaarde]) = BLANK(); 0 ; Related(Data_WBS_tbv_Beleidsportaal[Tekstwaarde)]/100); BLANK()); BLANK())In addition, if they not contains the relationship, you can try to use lookupvalue to find out the specific column value.
Regards,
Xiaoxin Sheng
Hello Anonymous,
The tables do have a relationship however when i try to use the Related() function it tells me the column doesn't exist or doesn't have a relationship to any table available in the current context.
I wanted to try out the LOOKUPVALUE() function however in my case im not sure how to use this function could you maybe help me out with this one. to simplify the column names etc the if statment would be smthing like this
if(table1[column1] = table2[column1];
if(table2[column2] = 2016007;
if(table2[column3] = BLANK();
0;
table2[column3]/100);
BLANK());
BLANK())
thank you for the help so far!
Hi Joshmarsteffens,
>>I wanted to try out the LOOKUPVALUE() function however in my case im not sure how to use this function could you maybe help me out with this one. to simplify the column names etc the if statment would be smthing like this
Sample formula: (calculate column)
Check= var column2= Lookupvalue(Table2[Column2],Table2[Column1],Table1[Column1]) var column3= Lookupvalue(Table2[Column3],Table2[Column1],Table1[Column1]) return IF(colummn2=2016007&&column3<>blank(),column3/100,0)
In addition, if above not help, can you please share me some sample data for these tables? I will write the formula based on them.
Reference link:
Regards,
Xiaoxin Sheng
