Forum Discussion
Show no data in table before search
Hi all,
I've created a report based on direct query.
I have the following requirement: I want to search on an specific id in a table but I don't want the table to load all the data beforehand.
See picture below, this is the basis that I have. A search bar and a table
I've greyed out the results here. So, like I mentioned earlier, I want only a result after I search for an id and start with an empty table.
Is there a possibility to do so?
Thanks.
Hi LL_
Daniel29195 's solution is a good start. It doesn't work here is probably due to that all IDs are filtered out by the measure filter at the first time. So when you search an id with the search bar, it is searching in the empty result from the first filtering, which lead to blank.
To deal with it, we need to make some changes. You need to have an additional table that has all Id values. Ensure this table is disconnected from the original table. Let's call this new table "Dim Id". Use id column from this new table into the search bar instead. Then use the following measure as a filter and set its value to 1 in the filter pane.
measure1 = if( ISFILTERED('Dim Id'[id]), IF( SELECTEDVALUE(tbl[id]) IN VALUES('Dim Id'[id]) , 1 , 0), 0)I have attached a sample file. Hope it would be helpful!
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
6 Replies
let us assume your table name is tbl and column name is id
create this measure :
measure =if( isfiltered(tbl[id]) , 1 , 0)
then add this measure in the filter pane on the visual filter level --> advanced filter --> is --> 1 --> click on apply.
let me know if this works for you .
If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠Hi Daniel,
Thanks for the solution, this doesn't load data in the table so that's a good starting point. However when I search for an id it still doesn't show data. Can I fix that in the formula as well?
LL_ Did you put the measure on the filter pane --> filter on visual section for the table visual ?
Hi LL_
Daniel29195 's solution is a good start. It doesn't work here is probably due to that all IDs are filtered out by the measure filter at the first time. So when you search an id with the search bar, it is searching in the empty result from the first filtering, which lead to blank.
To deal with it, we need to make some changes. You need to have an additional table that has all Id values. Ensure this table is disconnected from the original table. Let's call this new table "Dim Id". Use id column from this new table into the search bar instead. Then use the following measure as a filter and set its value to 1 in the filter pane.
measure1 = if( ISFILTERED('Dim Id'[id]), IF( SELECTEDVALUE(tbl[id]) IN VALUES('Dim Id'[id]) , 1 , 0), 0)I have attached a sample file. Hope it would be helpful!
Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
