Vlookup function as a measure and not a table

DAX DAX Calculations
1

Hi,

I recently joined this community. I hope my question is clear enough.

I would like to create a measure to multiply 2 different data from 2 different lookuptable.

Both tables are linked to my fact table. I would like to get a value from the first lookuptable and then get another value from the second lookuptable and multiply both results.

I could use lookupvalue to add 2 columns in my tables and then create a measure to multiply the 2 results but I prefer to create a measure instead of creating new data in my table.

In Excel, I would make 2 vlookup tables and the multiply both columns.

I cannot use RELATED function as I am unable to retrieve 1 row from an Iteration function without calculation. I only need the value of the 2 tables to multiply (am I missing something? Is a ITERATION function to get only the value?)

I was hoping to use variable to do this within the same measure

Can this be done within the same measure or should I retrieve the data in 2 different columns first?

If this can be done in the same measure, what are the formula needed?

Thanks for your support.
Jay

1 Like
2

@JayLocher ,

Welcome to the forum - great to have you here!

Using the eDNA Practice Dataset Tool, I created the attached example. The calculation doesn’t make much logical sense (customer index multiplied by quarter number), but I was simply looking for two numeric values from different dimension tables to match your requirement. Here’s the resulting measure:

Head Office Index x Quarter = 

VAR LookupIndex =
    LOOKUPVALUE(
        Customers[Head Office Index],
        Customers[Customer Index], SELECTEDVALUE( Sales[Customer Name Index] )
    )
VAR LookupQ =
    LOOKUPVALUE(
        Dates[QuarterOfYear],
        Dates[Date], SELECTEDVALUE( Sales[OrderDate] )
    )
VAR Result = LookupIndex * LookupQ
RETURN
    Result

I hope this is helpful. Full solution file attached.

1 Like
3

@BrianJ

Thank you very much for your prompt answer. This really helps and seems to work. In fact, in my model, it does not work but not because of your example. I realized that I do have another issue with cardinality Many-Many in my relationship.

I need to fix this as well, but this is a separate topic.

Thanks again for your help.
Regards
Diego

2 Likes
4

@JayLocher ,

Great- glad to hear that works for you (but for the M:M issue). With regard to that issue, here’s a recent thread where @Mike and I work step by step through his M:M problem that you may find helpful.

  • Brian
2 Likes
5

Hi @BrianJ ,

Not sure I understand why you believe the thread you send me is related to M:M problem. The 2 videos are for date table and offsets.

Thanks
Diego

6

@JayLocher ,

Sorry if that wasn’t clear. Yes, the videos are not relevant to your problem, but I thought the overall process that Mike and I went through over the entire thread in the data modeling to eliminate the M:M relationship in his model might be helpful to you as well.

– Brian