Is it possible to do a sub query based on a selected value?

[Anonymous]

Hi All,

I've been searching all over for an answer and failed to find whether it's possible to perform a nested query to find all clients returned from Table1 based on their Active status and use the results returned from Table1 to find a list of worker IDs in Table2. But the trouble I have is, have it as a dynamic report so each time a user selects a client status value in the slicer it will change the list of clients returned in Table1 and therefore list returned in Table2.

this is a simple question whether it's possible or not at all and have been troubled by this for days. 

[Anonymous]

Hi Eason,

I just noticed that the Table 1 is not filtered by that date range. Is it possible to have the appointment date range value applied to both Table 1 and Table 2? So both tables return appointments within 6/10 to 10/12. 

[Anonymous]

Sorry I could not figure how to load the .pbix file! 

[Anonymous]

 

[Anonymous · Accepted solution]

Hi Eason,

Thanks for following up, you're a star! ⭐

Basically in Table 1 (Find all the clients this worker ID has been in contact with) this is working as it should to return all the clients that worker W3 has visited during that time period selected. But my issue, in Table 2, is I want to return all the workers that each of the clients listed in Table 1 as a result of selecting worker W3 is from that date onwards.

For example, in Table 1 we see W3 worker saw Anne and Mandy both on 6/10 and 10/10. But in Table 2, it returns records that are earlier than the time range selected ie 5/10 which shouldn't be there. I confirmed it does do this despite my screenshot provided earlier. I have issues parsing the date parameter to the second table but the client parameter is not an issue because it uses the table relationship between ClientIDs from both tables.

Hope that makes sense. 

[Anonymous]

Thanks Eason that worked!!! Being new to Power Bi I didn't even think to combine my query to work with a filter card. 

[v-easonf-msft]

Hi, @Anonymous 

You can create a new measure as following and apply it to the visual filter pane.

Datefilter = IF(SELECTEDVALUE(Table2[AppointmentDate]) in VALUES(Table1[AppointmentDate]),1,0)

Best Regards,
Community Support Team _ Eason

[v-easonf-msft]

Hi, @Anonymous 

I don't see any errors in your screenshots, isn't it working properly?

Does your data all come from the same table?You can consider copying a new table and establishing the following relationship to filter the data.

Please check my attached sample file. Please let me know if I misunderstood anything.

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi Eason,

Thanks for attending to my question. 

I have attached the pbix file with the sample data. 

[Anonymous]

Genius! 

is there a way to return a table instead of a calculation in the second half?

calculate(Sum(Table2[value]), filter(Table2, Table2[user] in _tab))

Thanks so much this has really helped me! 

[amitchandak]

@Anonymous , You can create a measure like this one

measure =
var _tab = summzarize(filter(Table, Table[Status] ="Active"),[Client])
return
calculate(Sum(Table2[value]), filter(Table2, Table2[user] in _tab))

or

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

So just wondering is there a way to return a list of IDs from Table2 as a result of the results from Table 1? 

I can use a filter relationship in the data model  based on the IDs however it is a many-to-many relationship and I also need to filter it on date range.

[v-easonf-msft]

Hi,  @Anonymous 

Have you considered filtering the data directly after joining table 1 and table 2 into one table?

Best Regards,
Community Support Team _ Eason

[Anonymous]

Hi Eason!

yes, if I'm understanding your suggestion correctly. My understanding though is that the table doesn't regenerate a joined table each time a user selects some filters in the Table1. I tried using the selectedvalue() function within a FILTER function as well as the GENERATE() table function, but it didn't generate a table on a selected filter value. Unless i was using it wrong. 

[v-easonf-msft]

Hi,  @Anonymous

Please provide sample data/pbix files for further research, which will help us to better understand your problem.

Best Regards,
Community Support Team _ Eason