How to store filters results in a variable and use variable as filter ?

[patoku0207]

Hello

I  have a text filter where users can write a lot number and a table give the Item Numbers.

I want to store this lot number as a variable, so after I can filter the same table with this lot number on the receipt number

Item Number	Lot Number	Receipt Number
90123C	100025	123547
90123C	100026	123548
90123C	100027	123549
90123D	100045	100026
90123D	100046	100089
90123E	100085	100026
90123E	100087	100089
90123F	10024F	100010
90123F	10024G	100026
90123F	10024H	100087

Ex :  if I write 100026 in my  text filter I want to see : 

90123D	100045	100026
90123E	100085	100026
90123F	10024G	100026

Its like twice filters applied.

I cant use SELECTEDVALUES( function not recognized)  because I'm not in imported datas but I use direct query mode .

So How can I do that ?

Thanks in advance for your replies and sorry for my english, this is not my natural language

Pat

[patoku0207]

Hi and thanks for your answer.

I cant create a calculated table in Desktop ans i cant use SELECTEDVALUE because I use SSAS for my datas and direct query in Desktop

Best Regards

Patrice

[Anonymous]

Hi  @patoku0207 ,
You can test this by following the steps in my pbix file. If my answer is correct, please mark it as accept as solution.

Best regards

Albert He

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

[Anonymous]

Hi @patoku0207 ,
After my testing, both mixed mode (IMPORT & DIRECT QUERY), and through the live connection mode directly to SSAS, can create calculate table and use selectedvalue, values dax functions.

Best regards

Albert He

[Anonymous]

Hi @patoku0207 ,
According to your description, I created a similar table in SQL Server and then used Direct Query mode in desktop to get the data.

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

1.Create a calculat table (This step changes the storage mode to Mixed but does not affect the previous use of your Direct query.)

Table 2 = VALUES('Table'[Lot Number])

2.Create a flag Measure to apply to your table visual's filters to show the correct filtered data

Flag = 
var _lot1=SELECTEDVALUE('Table 2'[Lot Number])
var _rnum=SELECTEDVALUE('Table'[Receipt Number])
return if(VALUE(_lot1)=_rnum,1,0) 

3.Click the ... and get the text filter

4.Final output

Best regards

Albert He

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