Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
"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?
Solved! Go to 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 :
(note that i assume there is no difference in your type values whichever the dif values are)
i attached the ammended pbix file:
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
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.
Hi @artemus . I tried to enable filtering, also apply secturity filter in both direction.
I still have blank results in all cases.
attach your file and will take a look.
regards
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,
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 :
(note that i assume there is no difference in your type values whichever the dif values are)
i attached the ammended pbix file:
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
Thank you so much @YJ, I opted to create a calendar and building table as suggested. The method worked!
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
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?
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.
Thanks for checking @artemus . I applied SELECTEDVALUE and it returns blank for all rows. Any other idea?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |