Trying to lookup value in table that has duplicates

[Les111]

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

[ArwaAldoud · Accepted solution]

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

[Poojara_D12 · Accepted solution]

Hi @Les111 

I have attached a document that has few ideas about this issue, please check.

[Les111]

Thank you Poojara. 

[ArwaAldoud · Accepted solution]

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]

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]

You're welcome @Les111 

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

[lbendlin]

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

[Les111]

Yes it's report server.