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

[Anonymous]

"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?

[artemus]

If you want a simple (and potentially dangrous) solution. Just go to your relationship model and enable filtering in both directions. This should allow the various approaches to work, but can be a bit wonky.

[Anonymous]

Hi @artemus . I tried to enable filtering, also apply secturity filter in both direction.

I still have blank results in all cases.

[YJ]

attach your file and will take a look.

regards

[Anonymous]

Hi,

Please find the link for the file. The error is in the "Future dates" table, 2019 Bulding A - Type 1 column

https://drive.google.com/file/d/1F1vm8gcAt-3G_5dilL2esiNoOqNRCfN5/view?usp=sharing

Thanks,

[YJ · Accepted solution]

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

[Anonymous]

Thank you so much @YJ, I opted to create a calendar and building table as suggested. The method worked!

[YJ]

Hi,

for both to work, ideally the lookup must be a table with unique lookupvalues. 

In the case for lookupvalue, you can also specific "alternate result" in the event that result_columnName is filtered down to zero value or an error when more than one distinct value:

LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…[, <alternateResult>])

if you can uplopad your pbix file and it would be easier to see why.

regards

SK

[Anonymous]

Hi,

I tried to incorporate the alternate result to the LOOKUPVALUE formula. The result is also blank.

Most of the dates related with the ID I created are not unique. Any ideas?

[artemus]

Generally SELECTEDVALUE is the function you want to use here. It will return BLANK if there are multiple non-identical values that it could be.

[Anonymous]

Thanks for checking @artemus . I applied SELECTEDVALUE and it returns blank for all rows. Any other idea?