cancel
Showing results for 
Search instead for 
Did you mean: 
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
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors