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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Help with Vlookup using same column for search value and column value

Hi everyone,

 

I cant get my head around this problem in Power BI. I´m adding a new column to my table and want to check lookup if the same ID has value in both the column "F" and "L", but on different rows.

 

For example: The added column "Both F and L" should check if the same ID in the table has a value in both "F" and the "L" column, and if the same ID exist in the the table with the value "1" in both "F" and "L" then return 1 in the "Both F and L" column. I want to check if the customer has bought both the F and L product, but in different time periods.

 

My problem is I cant get the vlookup to work when using the same column for evertyhing (search value and search column). Dont want to create a new table for this.

 

IDFLBoth F and L
1101
2010
3100
1011

 

Regards,

Niclas

2 ACCEPTED SOLUTIONS

@Anonymous 

Please try

=
IF (
    TableName[ID]
        IN VALUES ( TableName[L] )
            && TableName[ID] IN VALUES ( TableName[F] ),
    1,
    0
)

View solution in original post

@Anonymous 
Please use

=
VAR CurrentIDTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[ID] ) )
VAR FValue =
    SUMX ( CurrentIDTable, TableName[F] )
VAR LValue =
    SUMX ( CurrentIDTable, TableName[L] )
RETURN
    IF ( FValue > 0 && LValue > 0, 1, 0 )

View solution in original post

11 REPLIES 11
tamerj1
Super User
Super User

@Anonymous 

Ok but why the result for the first ID 1 is 0?

Anonymous
Not applicable

Sorry, misstake from me

@Anonymous 

Please try

=
IF (
    TableName[ID]
        IN VALUES ( TableName[L] )
            && TableName[ID] IN VALUES ( TableName[F] ),
    1,
    0
)
Anonymous
Not applicable

Hi! Thanks, but unfortunalety it doesn´t work. One thing that might add more complexity is that a ID can have multiple rows with values in column "F" and "L", but shouldn affect your code above?

 

edit: Is the problem related to the value is not 1 in both "F" and "L" on the same row?

 

IDFLBoth F and L
1101
1011
1101
1011
1011
1011

 

 

 

@Anonymous 
This is the result I get out of this code

1.png

Am I missing something?

Anonymous
Not applicable

Hi,

 

No, it looks correct! Can there be some issue with the format of my table? The columns "F" and "L" are calculated columns in the datatype and format "Whole number". The ID columns is not calculated but has the same format and datatype. 

@Anonymous 
Try either

=
IF (
    TableName[ID]
        & ""
            IN VALUES ( TableName[L] )
                && TableName[ID] IN VALUES ( TableName[F] ),
    1,
    0
)

OR

=
IF (
    VALUE ( TableName[ID] )
        IN VALUES ( TableName[L] )
            && TableName[ID] IN VALUES ( TableName[F] ),
    1,
    0
)

Otherwise check for hidden charactors or spaces. Try to clean the column in PQ

Anonymous
Not applicable

Hi! I did some testing with sample ID. And the dax is only working when I put ID as 1 and not for example 5 or 1XXXX as some ID contains more than one number.

 

Niclasthell_1-1655190947766.png

 

 

Niclasthell_0-1655190905218.png

 

@Anonymous 

It seems I misunderstood tour requirement. I will modify the formula 

@Anonymous 
Please use

=
VAR CurrentIDTable =
    CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[ID] ) )
VAR FValue =
    SUMX ( CurrentIDTable, TableName[F] )
VAR LValue =
    SUMX ( CurrentIDTable, TableName[L] )
RETURN
    IF ( FValue > 0 && LValue > 0, 1, 0 )
Anonymous
Not applicable

Thank you really appreciated it!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors