Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
apmulhearn
Helper III
Helper III

Need Help Returning a Value Based on Contact ID Match and Date Filter Between Two Tables

Hi!

I have two tables - one with Queries by guests, one with Sales to guests.

 

I need to be able to count, within a given date range, how many Queries converted to Sales.

If a Sale date is BEFORE a Query date, that Sale does not count for the given query.

In the Sample provided, I am looking for the answer '5.'

apmulhearn_0-1665022307591.png


Thank you!
Amanda

2 REPLIES 2
amitchandak
Super User
Super User

@apmulhearn ,

Try a measure like

 

Sumx(Table1,

var _cnt = count(Filter(Table2, Table1[Contact ID] = Table2[ContactID] && Table2[Date] > Table1[Date]), Table2[Date])

return

_cnt)

 

Plot with columns from Table 1 columns

Hi @amitchandak , and thank you for this explanation...
When I get to the red part from your sample formula (repasted below) I am unable to add a table name - I can only add an existing measure. Do you have any suggestions for what I might look at? 

Sumx(Table1,

var _cnt = count(Filter(Table2, Table1[Contact ID] = Table2[ContactID] && Table2[Date] > Table1[Date]), Table2[Date])

return

_cnt)



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.