Solved: Related and LookupValue formulas doesn't work - bl... - Page 2

Anonymous · ‎10-21-2019

"I have 2 tables (A,B) that are Many to One related . I created a key id in each table to enable the relationship. That key is a concatenated function of a date and a name. e.g. :"Buildingname10/21/2019"

In the main table that I'm working (A), I tried to used a LookUpValue formula to get some specific data from the table B. I got the error: A table of multiple values was supplied where a single value was expected.

That same formula worked in another column of A to get data from a table C. Table A and C are one to one related.

Using the RELATED function to try to get the data from column B doesn't work either. The columns of B are not shown in the formula. I also created a fourth table (D) using FILTER and CALCULATE TABLE, with a many to one relation to A. The methods explained before didn´t work either.

Any ideas to solve this issue?

YJ · ‎10-22-2019

Hi,

wow, i can see a lot of effort esp on all the building keys.

There is few slight considerations to take note of:

1. your table2rev.building key is not unique. from what i see you want to get your type 1 value, those are repeated with same values because of column dif.

2. The measure you are using :

2019 Building A - Type 1 = LOOKUPVALUE('Table 2 -rev'[Type 1],'Future dates'[building A - Type 1 Key],CONCATENATE("Building A",'Future dates'[Date]))

The result column should normally always be in the same table as the search column, for lookupvalue

3. my personal opinion is that one should standardise your date format if you are going to concatenate it: from most of your example, the nearest i see is FORMAT(yourvalue,"D/M/YYYY"). i personally would have prefer DDMMYYYY to keep to same string length if indeed you have to see this as part of a building key.

The proposed rev measure in a direct ans to your question, taking the above 3 points in considerations:

REV1_2019 Building A - Type 1 = LOOKUPVALUE('Table 2 -rev'[Type 1],'Table 2 -rev'[building key],CONCATENATE("Building A",FORMAT('Future dates'[Date],"D/M/YYYY")),'Table 2 -rev'[dif],0)

I also format the same way in

Table2rev. building key = CONCATENATE('Table 2 -rev'[building],FORMAT('Table 2 -rev'[Date],"D/M/YYYY"))

(note that i assume there is no difference in your type values whichever the dif values are)

i attached the ammended pbix file:

PBIX

The above should ans to your question on why the lookupvalue do not work.

On a side note, though i yet to understand what your overall intention is. My opinion is your current model could be, imho, very difficult to maintain. For a neater solution one could consider to have a seperate distinct Building Table, calendar table and using calculate and dateadd to compare last year date and get values of type 1,2,3 etc. however this would be a seperate long conceptual qns.

meanwhile i hope it clear up the lookupvalue doubts you have.

regards

View solution in original post

Related and LookupValue formulas doesn't work - blank results. Please help

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024

How to Get Your Question Answered Quickly