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
Rumsch
New Member

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

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 🙂

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.  

vtangjiemsft_0-1721800651710.pngvtangjiemsft_1-1721800662392.png

(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.

vtangjiemsft_2-1721801121673.png

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. 

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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.  

vtangjiemsft_0-1721800651710.pngvtangjiemsft_1-1721800662392.png

(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.

vtangjiemsft_2-1721801121673.png

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. 

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

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.