Forum Discussion

tamara_nsb's avatar
tamara_nsb
Icon for Helper I rankHelper I
1 year ago
Solved

Using SelectedValue in SSAS direct query mode fails

Hi,  I need to do a filter on a data column that has combined data in the column using a slicer.   My report is in directQuery mode connected to an SSAS database and I can't get it to work.   Table...
Need Help
Shahid12523's avatar
Shahid12523
Icon for Community Champion rankCommunity Champion
1 year ago

In DirectQuery mode, SELECTEDVALUE with SEARCH or CONTAINSSTRING often fails because it can’t translate to SQL.

 

Workarounds:

 

Disconnected slicer + measure:

TaskFilter = IF(CONTAINSSTRING(MAX(Tasks[Task]), SELECTEDVALUE(Codes[Code])), 1, 0)


Then filter the table where TaskFilter = 1.

Preprocess Tasks: Split combined values into separate rows before loading.

Server-side view/query: Filter via SSAS view or function.

 

Measure-based filtering is usually the easiest in DirectQuery.

tamara_nsb's avatar
tamara_nsb
Icon for Helper I rankHelper I
1 year ago

This was the solution that worked for me.   But for some reason I can't accept as solution.   I am unable to upgrade or change my powerBi desktop version so that was not an option for me.    I got this working but only when I put the measure in the SSAS model and not in power bi.   

containsSelectedValue:=
VAR SearchTask = SELECTEDVALUE( MastList[Value])
RETURN
CALCULATE(
COUNTROWS('Table2'),
FILTER(
'Table2',
CONTAINSSTRING('Table2'[Value], SearchTask)
)
)

 

Then add a filter to the table that containsSelectedValue = 1