cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danferboc Frequent Visitor
Frequent Visitor

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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
YJ Regular Visitor
Regular Visitor

Re: Related and LookupValue formulas doesn't work. Please help

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.

Lookup01.JPG

 

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

 

Lookup02.JPG

(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

10 REPLIES 10
artemus Member
Member

Re: Related and LookupValue formulas doesn't work. Please help

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.

danferboc Frequent Visitor
Frequent Visitor

Re: Related and LookupValue formulas doesn't work. Please help

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

YJ Regular Visitor
Regular Visitor

Re: Related and LookupValue formulas doesn't work. Please help

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

danferboc Frequent Visitor
Frequent Visitor

Re: Related and LookupValue formulas doesn't work. Please help

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 Member
Member

Re: Related and LookupValue formulas doesn't work. Please help

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.

danferboc Frequent Visitor
Frequent Visitor

Re: Related and LookupValue formulas doesn't work. Please help

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

I still have blank results in all cases.

YJ Regular Visitor
Regular Visitor

Re: Related and LookupValue formulas doesn't work. Please help

attach your file and will take a look.

 

regards

danferboc Frequent Visitor
Frequent Visitor

Re: Related and LookupValue formulas doesn't work. Please help

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 Regular Visitor
Regular Visitor

Re: Related and LookupValue formulas doesn't work. Please help

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.

Lookup01.JPG

 

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

 

Lookup02.JPG

(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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 380 members 4,067 guests
Please welcome our newest community members: