Reply
HCC
Frequent Visitor

LOOKUPVALUE within same table???

Hi all,

 

I am having trouble creating a new calculated column that returns the value in one of my columns, based on filters from 3 different columns in different columns within the same table (See below):

 

HCC_0-1672353696499.png

 

My goal is to return the [month1_emplvl] "US000" code, found in the [area_fips] column, for the rest of the [area_fips] codes by their [Year & Quarter] and [Industry_Code]. The reason is because I would like to create a side by side comparison and run some more calculations.

 

I have spent a couple hours testing different functions like, SUMX, CALCULATE, FILTER, and LOOKUP with power BI returning "A table of multiple values was supplied where a single value was expected". I am not sure what this means but there are 125M rows in my dataset that I am retrieving from a dataflow. 

 

Any help is greatly appreciated!!! 

 

1 REPLY 1
v-xinruzhu-msft
Community Support
Community Support

Hi @HCC 

I have some questions that:

1. What the actual value you want to return, [area_tips] or [month1_emplvl]?

2.What does the "for the rest of the [area_fips] codes by their [Year & Quarter] and [Industry_Code" mean, what is the format it sholuld return?

Can you provide some expect result or more sample data? Is the picture i supplied  you want?

vxinruzhumsft_0-1672364784450.png

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

avatar user

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)