Forum Discussion

ReutAtias12's avatar
ReutAtias12
Frequent Visitor
3 years ago

return values from Intersect

Hi

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?

 

 

DAX
Intersect
Power BI

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    3 years ago

    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()) )

    • ReutAtias12's avatar
      ReutAtias12
      Frequent Visitor
      3 years ago

      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