Forum Discussion
Group by and get all IDS while Filtering a table
Hello Community,
I would like to get all IDS while filtering by code.
I have a dataset that has: Code, ID, Number, key
- there is a different Code for the same ID
- the same ID has the same Key
- And an ID has a consecutive number
I created a slicer to filter by a code but it gives only one row in ID:
I want a measure when you select a code you will get the whole list of the Ids.
Outcome result:
I see that way but it wont work for me: if (selectedvalue(data[code]) in ( the CODE of data[ID]), data[ID], 0)
I had in my mind to create an additional table but my data model is quite big, I would prefer a measure.
Thank you for reading.
pbix: https://drive.google.com/file/d/10gIjeRmUIe2xLFNNEjihBk8Q02mER5Lg/view?usp=sharing
Hi, unkCandy
You can try the following methods.
Table:
Table = VALUES(data[code])Measure:
Measure = IF(SELECTEDVALUE(data[code]) in VALUES('Table'[code]),1,0)Place the measure in the view with ID and set it equal to 1.
Is this similar to the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
10 Replies
Hi unkCandy ,
Not sure if I am missing anything but in the filter pane of the visual, you have the ID selected as 762.If you remove that your report should work alright.
Please let me know if this helps and mark is as the solution so that others can reach the solution faster.
Thank you,
Vishesh Jain
Thank for your reply, for the filter, I just want to show what it is the outcome will be, when I selected a code I can get all IDS that has the selected code in their code.
Hi unkCandy ,
Please can you check the below screenshot, if this is the solution that you are looking for.
Thank you,
Vishesh Jain
An additional table would work and you'd only need to keep the code and id columns.
Then you'd link that to your original table on id, change the filter direction to be New table -> Original table and then use the 'New table'[code] in your slicer.
Now when you choose a code in the slicer, it filters your original table by that code's id
How big is your data model? Do you think that would that be too much to add in?
yes, I tried adding new table but it will make the data model more complicated.
Hi UnkCandy,
I did not find the issue with a new table, as mentioned above you have a filter there, hence
Just don't summarise id and key and you should get the answer
Regards,
Ritesh
Hello thank you for your reply, what do you mean by not summarizing them? in the data fields?
Hi, unkCandy
You can try the following methods.
Table:
Table = VALUES(data[code])Measure:
Measure = IF(SELECTEDVALUE(data[code]) in VALUES('Table'[code]),1,0)Place the measure in the view with ID and set it equal to 1.
Is this similar to the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
