Slicer for searching text in unrelated table

[pgolbi]

I loaded data from Azure log analytics dataset in PowerBi. The data in the table is something like this: 

table - Log Analytics

Host	Server_Principal	ApplicationName	Text
Host A	user_1	Mashup Engine	Select * from DIM.Section
Host A	user_1	Microsoft Office	Select top 10 * from DIM.Section
Host B	user_3	Python	Select top 30 * from DIM.AssetLocation

Then loaded all table names from the database that log analytics monitors in to Objects table: 

table - Objects

Schema,Object
DIM,Action
DIM,AssetLocation
DIM,AssetStatus
DIM,BaseCode
DIM,RequestType

Object column turned in to slicer (with single selection).

What I am trying to do is to use slicer to find, show and calculate all records in Log Analytics table where selected table (for instance AssetLocation). Calculation works fine:

Total Count =

var _TotalCount = CALCULATE(COUNTROWS('Log Analytics'),

FILTER (

'Log Analytics',

CONTAINSSTRING ('Log Analytics'[Text], VALUES(Objects[Object])

)))

return

IF(ISBLANK(_TotalCount), 0, _TotalCount)

I can also find records and display by adding calculated column and then adding it to the visual (IsFound is True) 

IsFound = IF(

    ISERROR(

        SEARCH(CONCATENATE(VALUES(Objects[Object]),""), 'Log Analytics'[Text])

    ),

    TRUE(),

    FALSE()

)

Problem is that I must have Text column on the visual to calculate IsFound. I tried to cross join Objects[Object] with Log Analytics[Text] to marry them up but my Power BI Desktop goes to "Working on it..." forever. 

Can anyone please check if there is a better way to solve this?  I have 80K rows in Log Analytics table. 

[pgolbi]

Thank you, @YukiK 
I solved it by adding the crossjoin table on Objects[Object] and Log Analytics[Text] columns, then in Power Query used Text.Contains to generate a filter column, remove rows with where condition was not met and dropped the filter. Ended up with relatively small table that I added a couple of calculated columns and it did the trick. 
In Power Query (or M) it works faster compared to DAX. 

[YukiK]

@pgolbiAwesome! Great to hear! Did my suggestion help (if so, please give it a thumbs up 🙂)?

[YukiK]

Have you tried to add IsFound as a filter? That way, you won't have to include IsFound to the visual.