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
Elud89
Frequent Visitor

Complex Lookupvalue

Hi PowerBI Community,

 

I am trying to join two tables and populate a unique value based on a set of conditions. I would normally use lookupvalue to accomplish this, but there are multiple answers so it does not work.

 

In Table 1, I have an Item Code and 3 seperate uplift values based on a numeric range. In Table 2, we have the same Item Code as well as the actual value of the unit. I want to calculate the Uplift % in Table 2, based on the inputs from Table 1. 

 

Any up with creating a calculated column that does the bolded action in Table 2 below is greatly appreciated.

 

Table 1:

Item Code Cost From Cost To Uplift %
ABC $0.01  $499.99 10%
ABC $500  $999.99 8%
ABC $1,000  $1,000,000 5%
XYZ $0.01  $499.99 6%
XYZ $500  $999.99 5%
XYZ$1,000 $1,000,000 4%

 

Table 2:

Item Code Cost CALCULATED COLUMN / UPLIFT  %
ABC $526.24  8%
XYZ $1,124  4%

 

Hopefully that makes sense, let me know if I can clarify anything.

 

Thanks

1 ACCEPTED SOLUTION
danextian
Super User
Super User

Hi @Elud89 ,

 

I would use CALCULATE instead of LOOKUPVALUE for your use case.

Sample formula:

UPLIFT =
CALCULATE (
    MAX ( Table1[Uplift %] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Item Code] = EARLIER ( Table2[Item Code] )
            && EARLIER ( Table2[Cost] ) >= Table1[Cost From]
            && EARLIER ( Table2[Cost] ) <= Table1[Cost To]
    )
)

sample result

danextian_0-1661213606394.png

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

View solution in original post

6 REPLIES 6
danextian
Super User
Super User

Hi @Elud89 ,

 

I would use CALCULATE instead of LOOKUPVALUE for your use case.

Sample formula:

UPLIFT =
CALCULATE (
    MAX ( Table1[Uplift %] ),
    FILTER (
        ALL ( Table1 ),
        Table1[Item Code] = EARLIER ( Table2[Item Code] )
            && EARLIER ( Table2[Cost] ) >= Table1[Cost From]
            && EARLIER ( Table2[Cost] ) <= Table1[Cost To]
    )
)

sample result

danextian_0-1661213606394.png

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Hey danextian, your solution works, but I have an additional variable I wasn't taking into account in the original question. I was wondering if you were able to help with this new wrinkle.

 

Imagine there's a third Item, with the code, ABCD. There is no rule for ABCD in Table 1, but it would it would fall back and use the ABC rule. In this case, it would be 10%, is this still possible to calculate?

 

Thank you again in advance for your help,

 

Table 1:

Item Code Cost From Cost To Uplift %
ABC $0.01  $499.99 10%
ABC $500  $999.99 8%
ABC $1,000  $1,000,000 5%
XYZ $0.01  $499.99 6%
XYZ $500  $999.99 5%
XYZ$1,000 $1,000,000 4%

 

Table 2:

Table 2:

Item Code Cost CALCULATED COLUMN / UPLIFT  %
ABC $526.24  8%
XYZ $1,124  4%
ABCD $60.85 10%

Hi @Elud89 ,

 

You can write a condition so the uplift returns 10% if the item is ABCD

UPLIFT =
IF (
    Table1[Item Code] = "ABCD",
    0.10,
    CALCULATE (
        MAX ( Table1[Uplift %] ),
        FILTER (
            ALL ( Table1 ),
            Table1[Item Code] = EARLIER ( Table2[Item Code] )
                && EARLIER ( Table2[Cost] ) >= Table1[Cost From]
                && EARLIER ( Table2[Cost] ) <= Table1[Cost To]
        )
    )
)

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Appreciate the response, but that would work if that was the only one, but I'm using this calculated column on a large dataset and it will have more Item Codes than just what I mentioned above. It won't be sustainable to write that many if statements.

You can use LOOKUPVALUE to check whether Table2[Item] is found in Table1. If not found, use "ABC" else  Table2[Item].

UPLIFT = 
VAR ItemCode =
    LOOKUPVALUE ( Table1[Item Code], Table1[Item Code], Table2[Item Code] )
RETURN
    CALCULATE (
        MAX ( Table1[Uplift %] ),
        FILTER (
            ALL ( Table1 ),
            IF ( ISBLANK ( ItemCode ), "ABC", EARLIER ( Table2[Item Code] ) ) = Table1[Item Code]
                && EARLIER ( Table2[Cost] ) >= Table1[Cost From]
                && EARLIER ( Table2[Cost] ) <= Table1[Cost To]
        )
    )

danextian_0-1661303708332.png

 






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.

Hey! It looks like this worked. I'll have to keep playing around with it, but this is greatly appreciated. Thank you!

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.