Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I have a dataset with Names of volunteers and each one is given a unique ID number. In another table, I have some contacts and in that table, there is a column of with the Volunteer ID of the volunteers that are assigned to those contacts.
I am doing a LOOKUPVALUE to get the name of the Volunteer as another column in the Contacts table. It works for the most part but randomly gives back blank values when I know a name exists in the field.
Here is the formula:
Assigned Volunteer = LOOKUPVALUE(Volunteers[First Name],Volunteers[Volunteer ID],Contacts[Primary Volunteer ID])
It will do things like this (I know Volunteer ID 46 has a valid named as I have manually checked it):
Or it will do this (where for a given ID it will work sometimes but not others):
I have been stuck on this for hours. I have tried Related, Calculate with FirstNONBlank, etc but I CANNOT figure this out. Please Help!
Hi everyone,
I was getting empty spaces for some rows when using the lookupvalue formula between tables and changing relationships of those tables solved it.
I am not sure if following details make it more clearer:
BEFORE
Tables relationship (many to many): Masters.table_Column.A <---> Data.table_ColumnB
Lookupvalue function: Masters.table_Column.A <---> Data.table_ColumnG = giving empty spaces in some rows
AFTER
Tables relationship (many to many): Masters.table_Column.A <---> Data.table_ColumnG
Lookupvalue function: Masters.table_Column.A <---> Data.table_ColumnG = not giving any empty spaces now
Note:
In above scenario Data.table_ColumnG & Data.table_ColumnB had names but not in same order. These names could be found in Masters.table_Column.A
I have this problem too, where I'm using lookupvalue but for some results it always returns blanks even though I know data is present. And I can't figure out what's wrong since it works for maybe half the data so I don't think it would be a problem in the relationships.
I am also having this issue, where certain values are not returning results even though I've confirmed they do match data in the lookup column.
I tried using TRIM to rule out any leading or trailing spaces, with no luck. I've tried deleting the relationships to other tables in case that was interfering, but it did not help. It works for 95% of the data, but the remaining 5% return blank.
I have the same issue. A lookup randomly returns blanks for some rows.
Me encounter this problem. Me solve by turning off biderectional cross filtering on the table from which me was looking up from. Me guess me can live without that. We make sactifices in life. Should jus wrk. Shame shame. Naughty naughty
Did I help ou? please mark this post as answer if yes and remit @20000 bitcoin. Else, bwlo me
If you have a table with unique volunteers you should be able to do this by creating a relationship between the tables, and use the related() function.
Can you share an example of your data?
@darshp1 and you sure there are always values for the volunteers name in volunteers, ie you dont have duplicates, and one is blank and the other isn't. Also does it always do it for the same id's or is it random, so an id that worked today wont work tommorrow, is there consistency in the id's that throw up blanks?
Proud to be a Super User!
@vanessafvg I checked and rechecked the Volunteer IDs and they are all distinct values. Now some of the names are the same but that shouldn't matter.
It's the same ones happening over and over again. Even tried it on a different computer to see if there was a cache issue but same exact results.
@shaunwilks can you share your pbix?
Proud to be a Super User!
Unfortunately I cannot as it contains sentive data.
I will rebuild the model from the start and see how I go.
There is a join to the label I am using in the lookup so wonder if that is impacting
@darshp1have you tried to create a relationship between the two tables between the fields instead of a lookup.
Proud to be a Super User!
It must be something in the data.
Can you share it? in a DM perhaps?
I had the same issue, can you please advise how to fix the error, many thanks
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |