Forum Discussion
Is it possible to do a sub query based on a selected value?
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.
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.
14 Replies
- amitchandak
Super User
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.
- v-easonf-msft
Community Support
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
- 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 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.
- 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
