Forum Discussion
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!
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
1 NEW 2 HIRED 3 INTERVIEWED 4 INTERVIEWED 5 INTERVIEWED 6 HIRED 7 NEW 8 HIRED 9 HIRED 10 INTERVIEWED 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
1 NEW 2 NEW 2 INTERVIEWED 2 HIRED 3 NEW 3 INTERVIEWED 4 NEW 4 INTERVIEWED 5 NEW 5 INTERVIEWED 6 NEW 6 INTERVIEWED 6 HIRED 7 NEW 8 NEW 8 INTERVIEWED 8 HIRED 9 NEW 9 INTERVIEWED 9 HIRED 10 NEW 10 INTERVIEWED 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
1 NEW NEW null null 2 HIRED NEW HIRED INTERVIEWED 3 INTERVIEWED NEW null INTERVIEWED 4 INTERVIEWED NEW null INTERVIEWED 5 INTERVIEWED NEW null INTERVIEWED 6 HIRED NEW HIRED INTERVIEWED 7 NEW NEW null null 8 HIRED NEW HIRED INTERVIEWED 9 HIRED NEW HIRED INTERVIEWED 10 INTERVIEWED NEW null INTERVIEWED Then I would delete the "Status" column and "unpivot" the other 3 status columns - result:
Candidate Attribute Value
1 NEW NEW 2 NEW NEW 2 HIRED HIRED 2 INTERVIEWED INTERVIEWED 3 NEW NEW 3 INTERVIEWED INTERVIEWED 4 NEW NEW 4 INTERVIEWED INTERVIEWED 5 NEW NEW 5 INTERVIEWED INTERVIEWED 6 NEW NEW 6 HIRED HIRED 6 INTERVIEWED INTERVIEWED 7 NEW NEW 8 NEW NEW 8 HIRED HIRED 8 INTERVIEWED INTERVIEWED 9 NEW NEW 9 HIRED HIRED 9 INTERVIEWED INTERVIEWED 10 NEW NEW 10 INTERVIEWED INTERVIEWED 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..
This is a bit of a roundabout solution sorry.. hopefully someone has something easier. I hope this works for you
2 Replies
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
1 NEW 2 HIRED 3 INTERVIEWED 4 INTERVIEWED 5 INTERVIEWED 6 HIRED 7 NEW 8 HIRED 9 HIRED 10 INTERVIEWED 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
1 NEW 2 NEW 2 INTERVIEWED 2 HIRED 3 NEW 3 INTERVIEWED 4 NEW 4 INTERVIEWED 5 NEW 5 INTERVIEWED 6 NEW 6 INTERVIEWED 6 HIRED 7 NEW 8 NEW 8 INTERVIEWED 8 HIRED 9 NEW 9 INTERVIEWED 9 HIRED 10 NEW 10 INTERVIEWED 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
1 NEW NEW null null 2 HIRED NEW HIRED INTERVIEWED 3 INTERVIEWED NEW null INTERVIEWED 4 INTERVIEWED NEW null INTERVIEWED 5 INTERVIEWED NEW null INTERVIEWED 6 HIRED NEW HIRED INTERVIEWED 7 NEW NEW null null 8 HIRED NEW HIRED INTERVIEWED 9 HIRED NEW HIRED INTERVIEWED 10 INTERVIEWED NEW null INTERVIEWED Then I would delete the "Status" column and "unpivot" the other 3 status columns - result:
Candidate Attribute Value
1 NEW NEW 2 NEW NEW 2 HIRED HIRED 2 INTERVIEWED INTERVIEWED 3 NEW NEW 3 INTERVIEWED INTERVIEWED 4 NEW NEW 4 INTERVIEWED INTERVIEWED 5 NEW NEW 5 INTERVIEWED INTERVIEWED 6 NEW NEW 6 HIRED HIRED 6 INTERVIEWED INTERVIEWED 7 NEW NEW 8 NEW NEW 8 HIRED HIRED 8 INTERVIEWED INTERVIEWED 9 NEW NEW 9 HIRED HIRED 9 INTERVIEWED INTERVIEWED 10 NEW NEW 10 INTERVIEWED INTERVIEWED 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..
This is a bit of a roundabout solution sorry.. hopefully someone has something easier. I hope this works for you
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.