Forum Discussion

bjomol's avatar
bjomol
New Member
7 years ago
Solved

Lookup a value in two dimensions

Need Help
  • v-yulgu-msft's avatar
    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

3 Replies

    • bjomol's avatar
      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's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft 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