Lookup a value in two dimensions

[v-yulgu-msft]

Hi @bjomol ,

Please provide sample data and show us desired output to make your requirement more clear.

How to Get Your Question Answered Quickly

Regards,

Yuliana Gu

[bjomol]

Hi! 

I would like some help to make the following lookup in Power BI or Excel if needed.

The yellow cluster cells looks first for the country, then if cars and meters are in the two min/max intervals it returns the matching Cluster. The Clusters can be similar in car interval for example but different in meters.

Best regards, Björn

[v-yulgu-msft · Accepted solution]

Hi @bjomol ,

Add a calculated column as below.

Cluster =
CALCULATE (
    MAX ( Sheet6[Cluster] ),
    FILTER (
        Sheet6,
        Sheet6[Country] = EARLIER ( Sheet7[Country] )
            && Sheet6[cars min] <= EARLIER ( Sheet7[Cars] )
            && Sheet6[cars max] >= EARLIER ( Sheet7[Cars] )
            && Sheet6[meters min] <= EARLIER ( Sheet7[Meters] )
            && Sheet6[meters max] >= EARLIER ( Sheet7[Meters] )
    )
)

Best regards,

Yuliana Gu