USERRELATOINSHIP with one inactive one active field?

[Anonymous]

Hi everyone, 

I have two tables (attached). One table has a list of VIPs and the other has a table of customer transactions (to, from and $ amount). I want to filter visualizers based on if the VIP was involved in the transaction as a "to" or a "from." Since the VIP id # occurs in both to and from fields I've made two relationships. You can guess the problem I run into when trying to summarize in a table view: I get a summary for whatever relationship is currently active.

My objective is to have a summary that summarizes for both fields filtered against the VIP list. I've toyed around with USERRELATIONSHIP arguments to no avail. Some screen shots below on sample data.

Sample VIP Table:

Sample Transactions Table:

Data Model:

Output when to address active (left) and when from address active (right):

             

Any help would be appreciated.

[Barthel · Accepted solution]

Give this measure a try:

Total = 
VAR to_adress =
    CALCULATE (
        SUM ( Transactions[$ value] ),
        USERELATIONSHIP ( VIP[customerID], Transactions[to_adress] )
    )
VAR from_adress =
    CALCULATE (
        SUM ( Transactions[$ value] ),
        USERELATIONSHIP ( VIP[customerID], Transactions[from_adress] )
    )
RETURN
    to_adress + from_adress

[Anonymous]

Thanks @Barthel. I'm not as concerned with sum so much as I am the tables populating with each VIP. As you can tell from the screen shots, depending on which relationship is active, I get certain VIPs to appear in the table. I'd like them all to appear in the VIP[customer_id] column. Does that make sense or can I clarify further?

[Anonymous]

@Barthel tried to use the DAX you provided and got close (DAX TX Value), but seems like the problem still exists to some degree with (blanks). See below. I'd be happy to share the test file if that would help.

[Barthel]

The VIP table consists of 9 unique customer IDs. The transactions table consists of 18 unique customer IDs. The 9 IDs in the transaction table for which no match can be made are shown as '(Blank)' in the chart. Once you add all IDs to the VIP table that appear in the transaction table, '(Blank)' will disappear.

[Anonymous]

Ah, I see. @Barthel thank you so much for your help here. I really appreciate it along with your prompt responses. I hope you can help me with some other issues in the future as I build this out. Thanks again.

[Barthel]

The reason you don't see all VIPs is because there is no relevant value for that customerID (it is blank). 

2, 4 & 7 have no 'to address' value and 1, 3, 5, 6, 8 & 9 have no 'from address' value. To show all VIPs you have to place either both measures on the matrix (or a total).

Or you set that you would like to see the blank values. To do this, select the matrix, go to the format panel and click on the down arrow of the customerID column. Then select 'Show items with no data'.

Does this help you further?

[Anonymous]

@Barthel Thanks, I think this might work. In the example I pasted above, I filtered to exclude the (blanks) in the bar chart. Unsure why the double count occurs?

[tamerj1]

Hi @Anonymous 
Did you try adding both values, the one with USRELATIONSHIP and the one without is in one measure using + sign?

[Anonymous]

thank you for the reply @tamerj1 (you were a life saved with my prior issue last week!!). I clarified a bit in my response to Barthel above. The purpose of this is not to create a single aggregation measure, but to have those items display across the table visualizations. Happy to clarify