cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Filtering by counts of multiple values

Hello everyone,

 

First off, I apologize for not knowing how to describe my issue in 1 line on the subject title.  I will try to explain.

 

I want to build a recruitment conversion funnel.  I have one column which shows the step a candidate it at.  For simplicity's sake, say there are only 3 values that can be present:  NEW, INTERVIEW, HIRED.

 

The conversion funnel would show the number of people who were hired, interviewed and who were new.  The people Hired is simple (the value equals Hired).  The people Interviewed, however, would be the sum of the Hired counts and the Interviewed counts.  The people who were new is also simple - it's all of them.

 

To get this on a chart, I created three measures easily enough, and put them into the Funnel custom visual.  No issues here.

 

The issue is that when I select Interviewed (for example), the rest of the visuals will not update.  I get why (I'm using measures), but I cannot think of a way of arranging my data so that it works.  Behaviour I want is when Interviewed is selected, it will only show rows that have EITHER Interview or Hired in the step column.

 

I tried a calculated table (using a combination of calculate & allexcept) and connecting the two tables with a relationship, but I then have the reverse problem - the other filters don't update the conversion funnel!

1 ACCEPTED SOLUTION
dearwatson
Responsive Resident
Responsive Resident

My approach to solve this would be to look at the source table and try and get it into a format that supports the interaction

 

I presume it currently is a unique row for each candidate like this:

 

Candidate Status

1NEW
2HIRED
3INTERVIEWED
4INTERVIEWED
5INTERVIEWED
6HIRED
7NEW
8HIRED
9HIRED
10INTERVIEWED

 

To get the interaction you want you need the interviewed status to include the candidates that were subsequently hired. 

In the ideal world you will have a fact table that contains a row for each status, not each candidate... e.g it looks more like this:

 

Candidate Status

1NEW
2NEW
2INTERVIEWED
2HIRED
3NEW
3INTERVIEWED
4NEW
4INTERVIEWED
5NEW
5INTERVIEWED
6NEW
6INTERVIEWED
6HIRED
7NEW
8NEW
8INTERVIEWED
8HIRED
9NEW
9INTERVIEWED
9HIRED
10NEW
10INTERVIEWED

 

It would be way easier if you can get the data out of the source system in this format but if you can't then to make this from the first table I would use power query to first create an extra custom/conditional column for each of my statuses like this:

Candidate Status NEW HIRED INTERVIEWED

1NEWNEWnullnull
2HIREDNEWHIREDINTERVIEWED
3INTERVIEWEDNEWnullINTERVIEWED
4INTERVIEWEDNEWnullINTERVIEWED
5INTERVIEWEDNEWnullINTERVIEWED
6HIREDNEWHIREDINTERVIEWED
7NEWNEWnullnull
8HIREDNEWHIREDINTERVIEWED
9HIREDNEWHIREDINTERVIEWED
10INTERVIEWEDNEWnullINTERVIEWED

 

Then I would delete the "Status" column and "unpivot" the other 3 status columns - result:

 

Candidate Attribute Value

1NEWNEW
2NEWNEW
2HIREDHIRED
2INTERVIEWEDINTERVIEWED
3NEWNEW
3INTERVIEWEDINTERVIEWED
4NEWNEW
4INTERVIEWEDINTERVIEWED
5NEWNEW
5INTERVIEWEDINTERVIEWED
6NEWNEW
6HIREDHIRED
6INTERVIEWEDINTERVIEWED
7NEWNEW
8NEWNEW
8HIREDHIRED
8INTERVIEWEDINTERVIEWED
9NEWNEW
9HIREDHIRED
9INTERVIEWEDINTERVIEWED
10NEWNEW
10INTERVIEWEDINTERVIEWED

 

Now the table looks right! we have a superfluos column because I named the Column Name the same as the values - just delete one.

 

OK now you can do the thing you want to do! if you just have a basic DISTINCTCOUNT(Candidate[ID]) measure it will give you a working funnel with all the interaction you want..

 

Capture.PNG

 

 

This is a bit of a roundabout solution sorry.. hopefully someone has something easier. I hope this works for you

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

2 REPLIES 2
dearwatson
Responsive Resident
Responsive Resident

My approach to solve this would be to look at the source table and try and get it into a format that supports the interaction

 

I presume it currently is a unique row for each candidate like this:

 

Candidate Status

1NEW
2HIRED
3INTERVIEWED
4INTERVIEWED
5INTERVIEWED
6HIRED
7NEW
8HIRED
9HIRED
10INTERVIEWED

 

To get the interaction you want you need the interviewed status to include the candidates that were subsequently hired. 

In the ideal world you will have a fact table that contains a row for each status, not each candidate... e.g it looks more like this:

 

Candidate Status

1NEW
2NEW
2INTERVIEWED
2HIRED
3NEW
3INTERVIEWED
4NEW
4INTERVIEWED
5NEW
5INTERVIEWED
6NEW
6INTERVIEWED
6HIRED
7NEW
8NEW
8INTERVIEWED
8HIRED
9NEW
9INTERVIEWED
9HIRED
10NEW
10INTERVIEWED

 

It would be way easier if you can get the data out of the source system in this format but if you can't then to make this from the first table I would use power query to first create an extra custom/conditional column for each of my statuses like this:

Candidate Status NEW HIRED INTERVIEWED

1NEWNEWnullnull
2HIREDNEWHIREDINTERVIEWED
3INTERVIEWEDNEWnullINTERVIEWED
4INTERVIEWEDNEWnullINTERVIEWED
5INTERVIEWEDNEWnullINTERVIEWED
6HIREDNEWHIREDINTERVIEWED
7NEWNEWnullnull
8HIREDNEWHIREDINTERVIEWED
9HIREDNEWHIREDINTERVIEWED
10INTERVIEWEDNEWnullINTERVIEWED

 

Then I would delete the "Status" column and "unpivot" the other 3 status columns - result:

 

Candidate Attribute Value

1NEWNEW
2NEWNEW
2HIREDHIRED
2INTERVIEWEDINTERVIEWED
3NEWNEW
3INTERVIEWEDINTERVIEWED
4NEWNEW
4INTERVIEWEDINTERVIEWED
5NEWNEW
5INTERVIEWEDINTERVIEWED
6NEWNEW
6HIREDHIRED
6INTERVIEWEDINTERVIEWED
7NEWNEW
8NEWNEW
8HIREDHIRED
8INTERVIEWEDINTERVIEWED
9NEWNEW
9HIREDHIRED
9INTERVIEWEDINTERVIEWED
10NEWNEW
10INTERVIEWEDINTERVIEWED

 

Now the table looks right! we have a superfluos column because I named the Column Name the same as the values - just delete one.

 

OK now you can do the thing you want to do! if you just have a basic DISTINCTCOUNT(Candidate[ID]) measure it will give you a working funnel with all the interaction you want..

 

Capture.PNG

 

 

This is a bit of a roundabout solution sorry.. hopefully someone has something easier. I hope this works for you

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

Anonymous
Not applicable

Thank you for your thorough reply - I appreciate it!

 

I can extract historical steps from the source system, but unfortuantely the reporting tool errors out after 100k rows.  I already had to run multiple extracts for short date ranges and then append them to avoid this issue.  The thought of doing that that only many times more makes me ill 🙂  Moreover, there are alot more historical steps than I need, and due to business processes, some important ones can be skipped (for example, someone could skip interview step and go stright to hired, even though they were of course interviewed).  So I effectively lose counts there.

 

Anyway, I did exactly what you suggested and my report is working great now.  I was hoping to use some DAX-fu, but I kept failing in the end.  Creating multiple rows also gave me the opportunity to spot inconsistencies in some of my measures where I neglected to use distinct counts due to laziness.  Opportunity to fix those up as well.

 

Kudos.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors