Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Ucboy4
Regular Visitor

LOOKUP A VALUE FROM FORIGN TABLE AND RETURN A VALUE IN PRIMARY COLUMN IF RESULT IS BLANK

Hi

 

How do I fill up blank rows from a lookup function with values from a column in my primary table.

1 ACCEPTED SOLUTION
gauthamboppana
Solution Specialist
Solution Specialist

@Ucboy4   - There are few ways to do it.  You may create column or measure to get matched items using RELATED function, LOOKUPVALUE function, or FIRSTNONBLANK function .

 

Examples:

 

Column1=RELATED([one side field of relationship])

 

Column1= LOOKUPVALUE(tableA[goal field],  tableA[linked field],  tableB[linked field])) 

 

Column1= CALCULATE (FIRSTNONBLANK ( tableA[goal field], 1 ),FILTER ( ALLSELECTED( tableA), tableA[linked field] = tableB[linked field] ))


Once you look up the value from the foreign table. You can the use IF statement to check whether it is blank or non-blank. ANd, if it is blank, then get value from Primary table column. Example below:

 

Lookup =

Var ForeignValue =  LOOKUPVALUE(tableA[goal field],  tableA[linked field],  tableB[linked field])) 

Return
If (Isblank(ForeignValue), PrimaryTable[Column], ForeignValue)

Hope this helps !   If you need mroe help, please share the screenshots of the tables or upload a sample .pbix file through one drive. Happy to help.

Did I answer your question? Mark my post as a solution! If not, please feel free to ask me.

Also, I would ❤ Kudos if my solution helped.  It is a token of appreciation!

Thank you very much !

 

View solution in original post

3 REPLIES 3
Ucboy4
Regular Visitor

 

 

Perfect!!

 

thanks a million, it worked

@Ucboy4  - Glad it worked !

gauthamboppana
Solution Specialist
Solution Specialist

@Ucboy4   - There are few ways to do it.  You may create column or measure to get matched items using RELATED function, LOOKUPVALUE function, or FIRSTNONBLANK function .

 

Examples:

 

Column1=RELATED([one side field of relationship])

 

Column1= LOOKUPVALUE(tableA[goal field],  tableA[linked field],  tableB[linked field])) 

 

Column1= CALCULATE (FIRSTNONBLANK ( tableA[goal field], 1 ),FILTER ( ALLSELECTED( tableA), tableA[linked field] = tableB[linked field] ))


Once you look up the value from the foreign table. You can the use IF statement to check whether it is blank or non-blank. ANd, if it is blank, then get value from Primary table column. Example below:

 

Lookup =

Var ForeignValue =  LOOKUPVALUE(tableA[goal field],  tableA[linked field],  tableB[linked field])) 

Return
If (Isblank(ForeignValue), PrimaryTable[Column], ForeignValue)

Hope this helps !   If you need mroe help, please share the screenshots of the tables or upload a sample .pbix file through one drive. Happy to help.

Did I answer your question? Mark my post as a solution! If not, please feel free to ask me.

Also, I would ❤ Kudos if my solution helped.  It is a token of appreciation!

Thank you very much !

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors