Lead Funnel Modeling

[aaron1225]

Hello PBI Community - 

I am a bit of a newbie to PBI and looking at it for a solution to modeling a lead funnel.  My data consists of a series of lead stage changes stamped with dates.  Basically I want to know a count of how many leads were created, qualified, won, etc. MTD, QTD, YTD.  We use standard funnel stages from SFDC 

Lead Created

Marketing Qualified (MQL)

Sales Accepted (SAL)

Sales Qualified (SQL)

Won/Lost

Additonally I would like to filter leads that were produced from marketing activity such as lead source = email, paid search, incoming calls, orgainic vs. those creaded from sales lead source = cold calls.  

So far I have created a data model consiting of a dimDate, Sales Force Lead data (as shown in the example screenshot), and a lead ID table, but because of the time intellegence and multiple date relationships and filters can't seem to get it to work. Any tips on setting up my model, dax functions,etc would be appreciated.  

[Greg_Deckler]

OK, so it looks like you have multiple dates in your LeadFactTable that you are relating to your DateTable. This is a pretty common thing. One way to get around this issue is to use the USERELATIONSHIP function found here:

https://support.office.com/en-US/article/USERELATIONSHIP-Function-DAX-df723b4c-5bf4-4f47-bdd2-786877...

Another method is to use multiple Date tables, one for each date. I have had some success in then linking all of these data tables to yet ANOTHER date table and then using that final date table in visualizations. That gets really messy and confusing the more dates you have. You can just use the multiple date tables (without the "final" date table), but you will have to pay attention to which date table you are using in your visuals. Carefully name your date tables and the "Date" column differently to avoid gnashing of teeth.

Have a look here for building a similar type of thing with Microsoft CRM, it will give you some formulas that you might find useful.

https://www.linkedin.com/pulse/building-ultimate-microsoft-crm-dashboard-under-hour-greg-deckler?trk...

Also, I recommend DAX Patterns for your MTD, YTD type of stuff.

http://www.daxpatterns.com