Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
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:
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.
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 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.
VIP Table
Transaction Table
Data Model (link between ID and to/from)
Table Visual (with missing customerIDs)
@Anonymous , How is the second measure created?
count from address = calculate(count(Table[From Address]), userelationship(VIP[Address], Table[From Address]))
if you are using not summarized From Address, then that is not a use case for userelationship. you need two dimension tables
@amitchandak 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?
@Anonymous , You can make inactive as active using a measure only.
Blank can be because of that of missing values in VIP Table
@amitchandak 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:
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |