topic Re: Trying to lookup value in table that has duplicates in Report Server

Trying to lookup value in table that has duplicates

Les111 — Tue, 18 Feb 2025 09:36:35 GMT

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

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

Re: Trying to lookup value in table that has duplicates

lbendlin — Tue, 18 Feb 2025 13:47:26 GMT

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

Re: Trying to lookup value in table that has duplicates

Les111 — Tue, 18 Feb 2025 13:50:15 GMT

Yes it's report server.

Re: Trying to lookup value in table that has duplicates

ArwaAldoud — Tue, 18 Feb 2025 23:12:36 GMT

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

Re: Trying to lookup value in table that has duplicates

Poojara_D12 — Wed, 19 Feb 2025 06:15:40 GMT

Hi Les111

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

Re: Trying to lookup value in table that has duplicates

Les111 — Wed, 19 Feb 2025 08:25:33 GMT

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.

Re: Trying to lookup value in table that has duplicates

Les111 — Wed, 19 Feb 2025 08:27:53 GMT

Thank you Poojara.

Re: Trying to lookup value in table that has duplicates

ArwaAldoud — Wed, 19 Feb 2025 23:25:40 GMT

You're welcome Les111

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