topic Re: Slicer for searching text in unrelated table in DAX Commands and Tips

Slicer for searching text in unrelated table

pgolbi — Wed, 20 Oct 2021 06:02:40 GMT

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.

Re: Slicer for searching text in unrelated table

YukiK — Fri, 22 Oct 2021 02:03:06 GMT

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

Re: Slicer for searching text in unrelated table

pgolbi — Fri, 22 Oct 2021 03:00:04 GMT

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.

Re: Slicer for searching text in unrelated table

YukiK — Fri, 22 Oct 2021 14:15:39 GMT

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