Dynamic Measure to Return Different Values from Table1 related to Table2 by ID

[TotunG]

Hello, I have the below table that previously used a Calculated Column (Lookup Value to return a value from another table):

Table1:

ID	Month	Max Value
AB123	Jan	ERROR (Lookup Value Return Multiple)
AC124	Jan	ERROR (Lookup Value Return Multiple)
A912J	Feb	5
SKSAK	Apr	100

As can be seen, this is now returning an error for some rows due to returning multiple values. I know why it is returning multiple values - it is because I have added more data which spans across other years meaning that the 'ID' Lookup Value has now more than one occurrence and is therefore not unique (see below). Therefore, I would like a measure that will FILTER Table1 by the 'Date' slicer (which will then reduce the ID values to only one occurence) and then using row context of Table1, return the 'Max Value' for each ID (just like A912J and SKSAK) in Table1.

Table2:

ID	Item	Max Value	Min Value	Average	Date
AB123	Car	10	1	5	2020
AC124	Bike	20	10	16	2020
A912J	Truck	5	2	3	2020
SKSAK	Skateboard	100	10	70	2020
AB123	Car	20	5	15	2021
AC124	Bike	30	10	22	2021

[Anonymous]

Hi @TotunG ,

I create a sample pbix file(see the attachment), please check if that is what you want.  You can create a measure as below to get it:

Max Value from Table2 = 
VAR _seldate =
    MAX ( 'Table2'[Date] )
VAR _selid1 =
    SELECTEDVALUE ( 'Table1'[ID] )
RETURN
    CALCULATE (
        MAX ( 'Table2'[Max Value] ),
        FILTER ( 'Table2', 'Table2'[ID] = _selid1 && 'Table2'[Date] = _seldate )
    )

Best Regards

[TotunG]

Thank you for your help @Anonymous, this does work for MAX/MIN values. What if you were trying to just look up the ACTUAL value of something. Is there a way to replicate LOOKUPVALUE but within a Filter Context e.g. a year slicer affecting LOOKUP value?

[Anonymous · Accepted solution]

Hi @TotunG ,

Please update the formula of the measure as below and check if it can return your expected result...

Max Value from Table2 =
VAR _seldate =
    SELECTEDVALUE ( 'Table2'[Date] )
VAR _selid1 =
    SELECTEDVALUE ( 'Table1'[ID] )
RETURN
    CALCULATE (
        MAX ( 'Table2'[Max Value] ),
        FILTER ( 'Table2', 'Table2'[ID] = _selid1 && 'Table2'[Date] = _seldate )
    )

If the above one can't help you get the expected result, could you please provide more raw datain your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[amitchandak]

@TotunG , That will work best when only one value is returned

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8