Using SelectedValue in SSAS direct query mode fails

[tamara_nsb]

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 Codes

AA

BB

CC

Tasks

AA

AA,BB,CC

AA/CC

AA & BB

BB

I created a slicer from Codes[Codes],  which I want to filter the Task table.

I have tried Filter = SEARCH(SELECTEDVALUE('Codes'[Code]), SELECTEDVALUE('Tasks'[Task]), 1, -1)

Filter = CONTAINSSTRING(SELECTEDVALUE('Tasks'[Task]), SELECTEDVALUE('Codes'[Code]))

and tried assigning the slicer value to a measure and displaying it,  displays the value correctly.

I tried replacing the the slicer value with a hard coded value and that works correctly.

Does this just not work in Direct Query Mode?   I just wanted to filter on a slicer without splitting the data into multiple rows.    The data is not nicely formatted and trying to split might cause more issues by splitting lines that should not be split,  so I want to do a "contains"  match.   

Any ideas?    I saw a post about a text slicer but my desktop version does not have that,  and I am not allowed to use non-standard visuals.  

Returns this.   With slicer set to BB

Tasks,         Filter Value

AA	1
AA,BB,CC	1
AA/CC	1
AA & BB	1
BB	1

I expected to get,  which produces something I can use to filter the table.

AA	-1
AA,BB,CC	4
AA/CC	-1
AA & BB	6
BB	1

Any help or other solutions would be great.   

[v-venuppu]

Hi @tamara_nsb ,

Thank you @Shahid12523 for the prompt response.

May I ask if you have resolved this issue? Please let us know if you have any further issues, we are happy to help.

Thank you.

[Shahid12523]

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.

[v-venuppu]

Hi @tamara_nsb ,

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.

[v-venuppu]

Hi @tamara_nsb ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @KNP for the prompt response.

Even I was able to see the Preview Features in my system.You can find the below screenshot:

Check with the Admin Team or else if possible try to upgrade to latest version by downloading from Microsoft store.
You can find the below screenshot for the version that I have in my system for your reference:

Please let us know if you need any further assistance.

Thank you.

[tamara_nsb]

KNP,   thanks for responding.   But the approved version of PowerBi desktop that I am restricted to does not even have "preview features"  in our options.   So a no go.

[KNP]

Hi @tamara_nsb,

Some more details on your model and the relationships between your tables would be helpful.

The easiest solution may be to just turn on the new slicer options in preview features...

This will allow you to do a partial search.

It is a "standard" visual, so should be ok for you to use.

If you want the DAX solution, please provide additional model details.