Forum Discussion

bjomol

New Member

7 years ago

Solved

Lookup a value in two dimensions

Need Help

v-yulgu-msft

7 years ago

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

v-yulgu-msft

Microsoft Employee

7 years ago

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

New Member

7 years ago

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

Microsoft Employee

7 years ago

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