Forum Discussion

Les111's avatar
Les111
Icon for Resolver I rankResolver I
1 year ago
Solved

Trying to lookup value in table that has duplicates

I'm trying to lookup if a value exists in another table. This will determine whether to show/hide rows in the output based on whether there is a match.  

 

This is my data table:

GroupAndMetricID
InpatientM61
InpatientM61
InpatientM61
CommunityM61
CommunityM61
InpatientM59
CommunityM59

 

And my lookup table:

GroupAndMetric
InpatientM61
CommunityM61
InpatientM59
CommunityM59

 

So for each row in the data table I want to know if there is a matching value in the lookup table. I've tried these dax expressions, but get the errror 'a single value for column 'GroupAndMetricID' in table 'DataTable' cannot be determined...'

 

MatchColumn = IF(CONTAINS (DataTable, DataTable[GroupAndMetricID], LookupTable[GroupAndMetric] ),1,0)

 

MatchColumn = IF(LookupTable[GroupAndMetric] IN SELECTCOLUMNS ( RELATEDTABLE ( 'DataTable' ), DataTable[GroupAndMetricID]),1,0)

 

I have tried using measures and columns for this but still the same error. Can anyone suggest a solution?

Thanks

DAX
  • ArwaAldoud's avatar
    ArwaAldoud
    1 year ago

    You can solve this issue using LOOKUPVALUE or RELATED in DAX, or by creating a calculated column with EXISTS/LOOKUP functions.

    Using LOOKUPVALUE calculated column in your DataTable:

    MatchColumn = IF(
    NOT(ISBLANK(LOOKUPVALUE(LookupTable[GroupAndMetric], LookupTable[GroupAndMetric], DataTable[GroupAndMetricID]))),
    1, 0
    )

    If you have a relationship between the two tables, you can use Related:

    MatchColumn = IF(NOT(ISBLANK(RELATED(LookupTable[GroupAndMetric]))), 1, 0)

    this works only if LookupTable is related to DataTable on GroupAndMetricID.


    - Ensure LookupTable has unique values in GroupAndMetric (no duplicates).
    - If there’s no direct relationship, use LOOKUPVALUE instead of RELATED.

    Try these solutions and let me know if you need further clarification

    If this response was helpful, please accept it as a solution and give kudos to support other community members

7 Replies

  • ArwaAldoud's avatar
    ArwaAldoud
    Icon for Super User rankSuper User
    1 year ago

    You can solve this issue using LOOKUPVALUE or RELATED in DAX, or by creating a calculated column with EXISTS/LOOKUP functions.

    Using LOOKUPVALUE calculated column in your DataTable:

    MatchColumn = IF(
    NOT(ISBLANK(LOOKUPVALUE(LookupTable[GroupAndMetric], LookupTable[GroupAndMetric], DataTable[GroupAndMetricID]))),
    1, 0
    )

    If you have a relationship between the two tables, you can use Related:

    MatchColumn = IF(NOT(ISBLANK(RELATED(LookupTable[GroupAndMetric]))), 1, 0)

    this works only if LookupTable is related to DataTable on GroupAndMetricID.


    - Ensure LookupTable has unique values in GroupAndMetric (no duplicates).
    - If there’s no direct relationship, use LOOKUPVALUE instead of RELATED.

    Try these solutions and let me know if you need further clarification

    If this response was helpful, please accept it as a solution and give kudos to support other community members

    • Les111's avatar
      Les111
      Icon for Resolver I rankResolver I
      1 year ago

      Thank you Arwa!

       

      Both these solutions work, but I'm using RELATED rather than LOOKUPVALUE as my tables are related and I'm assuming this is more efficient.  

       

       

       

      • ArwaAldoud's avatar
        ArwaAldoud
        Icon for Super User rankSuper User
        1 year ago

        You're welcome Les111 

        Yes, RELATED is more efficient when tables are already connected. Glad it worked

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    Please confirm if this is indeed for Report Server, or for a semantic model in the Power BI Service?