Userelationship to return specific text/value

Question

Hi there,&nbsp;&nbsp;Got some great help with a prior post where the foundational issue is very similar. The main difference between the two (I believe) is the desired output here is a Text field whereas prior it was the summation of a value field. To paraphrase from my prior post:&nbsp;I have two tables (attached). One table has a list of VIPs [customerID]'s and the other has a table of customer transactions. I intend to build a "schedule" of transactions based on the day the transaction occured. While it might seem somewhat redundant based on the small test case provided, I'd like to include in the schedule a column for my VIP IDs alongside the date, the to address and the from address.&nbsp;&nbsp;&nbsp;Since the VIP id # occurs in both to and from fields I've made two relationships in my data model. As expected, when I go to make a table visual the VIP[customerID] is only shown for whatever the active relationship is across the schedule, but shows as blank where the inactive relationship exists. I'd love some guidance on how to get the VIP[customerID] to show up across all transactions only&nbsp;where they occur in the to or from columns (e.g., if neither the to or from contain a VIP I'd like to exclude those from the table visual). Supporting data is below. I've tried primarily IF statements based off max(customerID) paired with userelationships and well as some variable runs to return max of customerIDs in both relationship states, but no success. Any help would be grateful.&nbsp;VIP Table&nbsp;Transaction Table&nbsp;Data Model (link between ID and to/from)&nbsp;Table Visual (with missing customerIDs)&nbsp;

amitchandak · Answer

Anonymous , How is the second measure created? 
&nbsp;
count from address = calculate(count(Table[From Address]), userelationship(VIP[Address], Table[From Address]))
&nbsp;
if you are using not summarized From Address, then that is not a use case for userelationship. you need two dimension tables
https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi
&nbsp;

anonymous · Answer

amitchandak&nbsp;Thanks! Have not created a measures in this case. Just a log of transactions. In the table I've simply added those fields outlined. Ideally, the customerID column would have no blanks in it, but given the active/inactive relationships they do. Does that make sense?

amitchandak · Answer

Anonymous , You can make inactive as active using a measure only. 
Blank can be because of that of missing values in VIP Table

anonymous · Answer

amitchandak&nbsp;I think that's what I'm looking for... a measure to populate the customerID column. The customerID column is blank in some places because of the inactive/active relationship. For instance, if you look at the first row customerID 9 is in the VIP table, but yet not populated in the customerID column. Some things I've tried are below:&nbsp;New Customer ID = //this measurement is needed to address the active/inactive relationship pair in our whales_lookup table to transfers table
    VAR to_address = 
        CALCULATE(
            Max(VIP[customerID]),
                USERELATIONSHIP(VIP[customerID],transactions[to_address]) // this creates a temporary and simultaneous active relationship between whales and to address
        )
    VAR from_address = 
        CALCULATE(
            Max(VIP[customerID]),
            USERELATIONSHIP(VIP[customerID],transactions[from_address]) // this creates a temporary and simultaneous active relationship between whales and from address
        )
        RETURN

max(to_address, from_address)

Forum Discussion

Userelationship to return specific text/value

4 Replies

Related content

RLS and USERELATIONSHIP problem

Dax Measure to get specific text value

Text slicer: pasting multiple values

USERELATIONSHIP problems with n:m-relationship

DAX function to count specific text values from a column