Creating a variable as filter based on a sql subselect in power bi

[Rumsch]

Hey everyone, i'm comming from oracle pl/sql and i'm building a dashboard in power bi desktop. I connected an oracle db and what i need to do is to create a variable that can be used to filter unique id's. The data looks like this:

Id, prev, next, date prev, date next

A1,  nl, ger, 01.01.24, 03.01.24

A1, ger, swe, 03.01.24, 10.01.24

A1, swe, fr, 10.01.24, 15.01.24

A2, fr, ger, 02.02.24, 10.02.24

A2, ger, us, 10.02.24, 20.02.24

A3, us, can, 17.01.24, 30.01.24

A3, can, ger, 30.01.24, 10.02.24

And so on....

In sql the statement looks like this:

Select sq1.*

from table sq1

, (select id from table where prev='ger') sq2 

Where sq1.id=sq2.id

So i need to filter all rows containing the id from the table by useing a subselect. 

In addition to that, i need a filter in the dasboard, where the user can fill in the prev='ger' as a variable. In pl/sql i use a stored procedure with variables and do a call like call p_1(var1, var2, var3); 

I've seen some similar questions regarding subselects but not in combination with variable filters. Thx in advance 🙂

[Anonymous · Accepted solution]

Hi @Rumsch ,

Sorry, so far, to my knowledge, this may not be achieveable.

I suggest you create a slicer to manually filter the [prev].

(1) This is my test data.  

(2) We can create a measure.

Flag = IF(MAX('sq1'[Id]) in VALUES('sq2'[Id]),1,0)

(3) Place [Flag=1] on the screening of the visual object.

You can manually select the value on the [prev] slicer, with prev=ger in the screenshot, and the table will output the ids that exist in both the sq1 and sq2 tables with prev=ger.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Rumsch]

Hey:) thx a lot. I didn't expect an answer that fast and usefull as yours! Yes i think this will do the job. And the menu with the available slicers looks good, too. Meanwhile, since i was born with sql;) i created a column in the result table (on db) with the listagg function. So every prev and next combo is in this column as a string. Now i can use an advanced filter and type the keyword into the prompt. Not the best nor fastest solution but it works too. I implement your solution where it's possible (not too many possible keywords). Thx a lot