DAX column that finds a partial value in another table

DAX DAX Calculations
1

I have two files that I can’t connect with a relationship. But one file has partial values that I need to link to the other file.

I’ve added a test PBX. The badchad has a (True) because it is in the second table. But I need the new cell to say True for chadblack, chadsharpe and jimjones because they contain “chad” and “jim” which are in the lookup table.
Partial Lookup.pbix (17.7 KB)

3

Hi @chad.sharpe ,

I tried my best, but didn’t find a more elegant solution. Please try this:

First, you will need another table

Test =

var __firsttable = ADDCOLUMNS(
GENERATE('Lookup Data',SUMMARIZE('Master Data Set','Master Data Set'[Master Data])),
"testit",CONTAINSSTRING('Master Data Set'[Master Data],'Lookup Data'[Lookup Data]))

var __secondtable = SUMMARIZE(__firsttable,'Master Data Set'[Master Data],[testit])

return

FILTER(__secondtable,[testit]=TRUE())

Second step add test in master data set

Test =

var __true = LOOKUPVALUE(Test[testit],Test[Master Data],'Master Data Set'[Master Data])

return

IF(ISBLANK(__true),FALSE(),__true)

This is how I made it work.

After all, the problem was, that PowerBI has to check for each individual entry, if it fits the criteria. As there is no table that connects the two tables, you have to generate one.

Hope that works for you and maybe someone can build a more elegant solution upon my idea?

Sabine

2 Likes
4

I copied over the measures and It works… :slight_smile:
I just need to go through them and figure out how it works…lol

Thanks

1 Like