return values from Intersect

ReutAtias12 — Mon, 05 Dec 2022 10:19:13 GMT

I'm trying to get the LeadID that intersects two tables

I created this measure:

Measure = VAR _StartDate = CALCULATE ( MIN ( FactMarketingFunnel[StartDate] ), ALL ( FactMarketingFunnel ), USERELATIONSHIP ( FactMarketingFunnel[StartDate], dimDates[Date] ) ) VAR _EndDate = CALCULATE ( MAX ( FactMarketingFunnel[StartDate] ), ALL ( FactMarketingFunnel ), USERELATIONSHIP ( FactMarketingFunnel[StartDate], dimDates[Date] ) ) VAR _main = CALCULATETABLE ( SUMMARIZE ( FILTER ( FactMarketingFunnel, FactMarketingFunnel[Stage] = "Leads" && FactMarketingFunnel[StartDate] >= _StartDate && FactMarketingFunnel[StartDate] <= _EndDate ), FactMarketingFunnel[Lead_ID], FactMarketingFunnel[Lead_Created_Date] ) --, ) VAR _int = CALCULATETABLE ( SUMMARIZE ( FILTER ( FactMarketingFunnel, FactMarketingFunnel[Stage] = "Opportunities Won Opps" && FactMarketingFunnel[StartDate] >= _StartDate && FactMarketingFunnel[StartDate] <= _EndDate ), FactMarketingFunnel[Lead_ID], FactMarketingFunnel[Lead_Created_Date] ) ) RETURN INTERSECT ( _main, _int )

how can I return the relevant LeadID?

Re: return values from Intersect

amitchandak — Mon, 05 Dec 2022 15:21:56 GMT

ReutAtias12 , Assume you have a common table lead with lead ID and measure from 2 tables joined to it say M1 and M2

then intersect is

countx(values(Lead[Lead ID]), if(not(isblank([M1])) && not(isblank([M2])) , [Lead ID], blank()) )

Re: return values from Intersect

ReutAtias12 — Mon, 05 Dec 2022 15:34:42 GMT

Hi,

M1 and M2 are virtual tables that I'm creating in the measure

They are supposed to be dynamic base on the user filters for the stage field

topic return values from Intersect in DAX Commands and Tips

return values from Intersect

Re: return values from Intersect

Re: return values from Intersect