Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |