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!
Need help!
If someone can solve it I would be very greatful!
I have 2 tables.
Table one:
Has customer_id, session, session_date, session_group.
Table two:
Has customer_id_, invoice_sum, invoice_date.
I need DAX that looks up value in table two when session_group = "4" and takes invoice_sum value to table one.
Also I need conditions that customer_id in table 1 = customer_id in table 2.
Tricky part is that session_date and invoice_date dont match always and mostly session date is after invoice date.
So I need also a condition that looks up first invoice_sum that is before session_date (starting form session_date).
Thank you in advance!
Solved! Go to Solution.
I think the below will work as a calculated column on Table 1
invoice_sum =
IF (
'Table 1'[session_group] = 4,
VAR currentCustomer = 'Table 1'[customer_id]
VAR currentSession = 'Table 1'[session_date]
VAR invoiceSum =
MAXX (
CALCULATETABLE (
TOPN ( 1, 'Table 2', 'Table 2'[invoice_date] ),
'Table 2'[customer_id] = currentCustomer
&& 'Table 2'[invoice_date] <= currentSession
),
'Table 2'[invoice_sum]
)
RETURN
invoiceSum
)
I think the below will work as a calculated column on Table 1
invoice_sum =
IF (
'Table 1'[session_group] = 4,
VAR currentCustomer = 'Table 1'[customer_id]
VAR currentSession = 'Table 1'[session_date]
VAR invoiceSum =
MAXX (
CALCULATETABLE (
TOPN ( 1, 'Table 2', 'Table 2'[invoice_date] ),
'Table 2'[customer_id] = currentCustomer
&& 'Table 2'[invoice_date] <= currentSession
),
'Table 2'[invoice_sum]
)
RETURN
invoiceSum
)
Thank you so so much!
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
19 | |
15 |