Single Filter for PowerBI

Question

I have 2 tables in powerbi, one contains all transactions to and from people (each client identified with an id, where "I" can be either the receiver or sender of $) and the other is the detail for each client.

Table 1 would look something like

| $ | sender id | receiver id |
|---|-----------| ------------|
| 10|     1     |       2     |
| 15|     1     |       3     |
| 20|     1     |       2     |
| 15|     3     |       1     |
| 10|     3     |       1     |
| 25|     2     |       1     |
| 10|     1     |       2     |

The second table contains sender id and name:

| id | name  |
|----|-------|
|  1 | "me"  |
|  2 | John  |
|  3 | Susan |

The expected result is something like (not necesarily in a table, just to show)

| $ sent | $ received | Balance|
|--------|------------|--------|
|   55   |     45     |   +10  |

And in a filter have "John" and "Susan" so when i Select one of them i could see $ sent, $received and balance for each of them.

The problem of course is that i end up with one active and one inactive relationship so if i apply such a filter i end up with 0 in sender/receiver and the whole value in the other (depending which is made active and inactive) and if i make another table that's "id sender"+"name sender" then i cant filter all at once.

Is it possible to do this? I hope this is kinda understandable

Answer 1

You will need to add 2 columns to your user table

received = CALCULATE(SUM(T1[$]), Filter(T1, UserTable[id] = T1[reveicer id]))

The same you can do for send. Now in your visual, use the new columns.

Enjoy!

Answer 2

after going around a bit I found a way to solve this, probably not the most orthodox way to do it, but it works.

What I did is to add 2 columns to my sales table, one was labeled "movement" and in sql it is just a 'case' where when the receiver is 'me' its "Charged" and when the receiver is 'not-me' its "Payment", then i added a column with a case so it would always bring me the 'not-me' id, and i used that for may relationship with my users table.

Then I just added filters in my cards making one a "Payment" card and the other a "Charged" card.

This is all following the previous example, it was actually just a bit more tricky as I could actually have a payment from me to myself, but thats just another "case" for when it was 'me-me'

Hope this is understandable, english is not my first language and the information i actually used is partially confidential so i had to make the above example.

thanks all and have a nice day.