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

Accepted Solutions
dearwatson Responsive Resident
Responsive Resident

Re: Filtering by counts of multiple values

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

Re: Filtering by counts of multiple values

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

Re: Filtering by counts of multiple values

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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors