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
NGTaylor
Frequent Visitor

Counting Stages of Records

I'm desperate for help on counting status stages of Leads if anybody could help.

 

I have a table of leads with Lead Number, Created Date, Lead Status, No. days in that status, Lead Tracking Status, Country, Business Unit and Campaign Name.  I'd like to create a measure (I think) that counts the number of leads that have been in each status.  The difficulty arises when I need to count each lead in its status, even if the system didn't record that stage.

 

There are four sequential status stages, with Rejected a non-sequential stage:

  • Enquiry
  • Marketing Qualified Lead
  • Sales Qualified Lead
  • Sales Accepted Lead
  • Rejected

The end result is either Sales Accepted Lead or Rejected and some leads will be in progress, ie: at stage 1, 2 or 3.

If the result is SAL, then I would want to record a count in each of the three previous stages, even if it doesn't exist as a row.  For a lead that has been properly Sales Accepted, it should count as an Enquiry, a Marketing Qualified Lead, a Sales Qualified Lead and a Sales Accepted Lead but if it only has two rows (eg: Enq and SAL or MQL and SAL) I would still want to count it in each stage.

 

If the result is Rejected, then it should count against the stages that have been recorded or any earlier stages not recorded but should have been.  ie: If Lead Number 1 has rows for Enquiry and Rejected, then it should only count in Enquiry.  If Lead Number 2 has rows for Enq, SQL and Rejected, then it should count in Enq, MQL and SQL.

 

Sorry for the confusing description.  Screenshot of the table is below.

 

I've tried a few things but nothing gives me the right results.

 

 

Lead Table.JPG

 

1 ACCEPTED SOLUTION
v-chuncz-msft
Community Support
Community Support

@NGTaylor,

 

Add a calculated column to return 1, 2, 3, 4 and 0 for each stage, then you may take advantage of the maximum value.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-chuncz-msft
Community Support
Community Support

@NGTaylor,

 

Add a calculated column to return 1, 2, 3, 4 and 0 for each stage, then you may take advantage of the maximum value.

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.