This is my first post in the PowerBI community, so apologies if the format isn't perfect. As far as I know, this question hasn't been posted yet.
I have a table with client IDs, and each client has specific values. I would like to create a filter that, when I select "Value = B", shows me a table containing the client ID and the values for all clients who have any mention of value B.
For example, it would show all values for Clients 1, 2, and 3, but not for Client 4, since Client 4 doesn't have a value of B. I would ideally have a slicer where I can choose 4, so this will allow me to change the value that I need (I have around 500 choices in my table).
If I were to write this in SQL, it would look something like the query below. Despite my best efforts, I just can't figure out how to achieve this in PowerBI.
select ClientId, Value
from table
where clientid in (select * from table where Value = B)
This is the table that I have:
| Clientid | Value |
| 1 | B |
| 1 | C |
| 1 | D |
| 1 | F |
| 1 | G |
| 1 | I |
| 1 | J |
| 2 | B |
| 2 | E |
| 2 | F |
| 2 | H |
| 2 | I |
| 2 | J |
| 3 | A |
| 3 | B |
| 3 | D |
| 3 | F |
| 3 | H |
| 3 | J |
| 4 | A |
| 4 | C |
| 4 | E |
| 4 | G |
| 4 | I |
| 4 | J |
And this is what I am after (ClientId 4 is therefore gone because it does not have the value B)
| Clientid | Value |
| 1 | B |
| 1 | C |
| 1 | D |
| 1 | F |
| 1 | G |
| 1 | I |
| 1 | J |
| 2 | B |
| 2 | E |
| 2 | F |
| 2 | H |
| 2 | I |
| 2 | J |
| 3 | A |
| 3 | B |
| 3 | D |
| 3 | F |
| 3 | H |
| 3 | J |
My method so far
I create a copy of the table in DAX and making sure I have distinct values.
NewTable = DISTINCT(SELECTCOLUMNS('Table', "Value", 'Table'[Value], "Clientid", 'Table'[ClientId]))
Then I create a measure that reads from NewTable when select B, and attemps to export the list of ClientID
Filtered_ClientID =
CALCULATETABLE (
DISTINCT('Table'[ClientId]),
FILTER (
'Table',
'Table'[Value] IN VALUES ( 'NewTable'[Value] )
)
)
The problem is that (1) I feel like I'm going in circle with my problem and (2) This Filtered_ClientID is throwing an error that there are multple value per column, which is exactly what I am trying to extract.
Thanks a lot! By the way, how come I cannot insert a PowerBI file into the chat... isnt it the PowerBI forum? /s
