Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
pgolbi
Helper I
Helper I

Slicer for searching text in unrelated table

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

 

table - Log Analytics

HostServer_PrincipalApplicationNameText
Host Auser_1Mashup EngineSelect * from DIM.Section
Host Auser_1Microsoft OfficeSelect top 10 * from DIM.Section
Host Buser_3PythonSelect 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. 
 
 

 

3 REPLIES 3
pgolbi
Helper I
Helper I

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
Impactful Individual
Impactful Individual

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

YukiK
Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.