Data from other columns is not adjusted after unpivot in powerbi

Question

My table looks like this:

Candidate   |Current Status | Interviewer 1 | Interview 1 Date | Interviewer 2 | Interview 2 Date
Candidate 1 | Int1 clear    | aaa           | 1/1/2020         | bbb           | 2/1/2020 <br>
Candidate 2 | Int1 pending  | bbb           | 10/1/2020        | aaa           | 10/2/2020 <br>

There're more columns but I'm ignoring them for now.

I want to create a view to find out how many interviews were conducted by "aaa" drill down to interview date and the current status. Issue is, "aaa" will be shown for both Interview 1 & 2.

I tried to unpivot for Interviewer 1 and Interviewer 2, but that gives me the irrelevant dates of Interview by "bbb". Something like,

Candidate 1 | Int 1 clear   | 1/1/2020 | 2/1/2020 | Interviewr 1 | aaa<br>
Candidate 1 | Int 1 clear   | 1/1/2020 | 2/1/2020 | Interviewr 1 | bbb<br>
Candidate 2 | Int 1 pending | 1/1/2020 | 2/1/2020 | Interviewr 2 | aaa<br>
Candidate 2 | Int 1 pending | 1/1/2020 | 2/1/2020 | Interviewr 2 | bbb<br>

Now there's data (Interview 2 date) of aaa for interviews conducted by bbb.

Clarification - Interview 1 and Interview 2 are of same candidate. Candidate is going through series of interview so we're trying to keep the track of the candidate and the interviews they go through.

Each interview is taken by a different panelist - I want to count the number of interviews taken by the panelist and drill down to the details of each Interview

Answer 1

I don't exactly know what it is you want to do since your explanation is somewhat vague. If I understand you correctly you might be better of giving Interviewer labels to the correct Interview by hand. For example: (this is without unpivoting)

Interview  |Interviewer|Candidate. |status
____________________________________________
Interview 1|aaa.       |Candidate 1|Pending
Interview 2|bbb.       |Candidate 2|Pending
Interview 3|aaa.       |Candidate 3|Clear
and so on

Or, you could also try making interviewer columns like the following:

aaa.       |bbb.       |Candidate. |status
____________________________________________
Interview 1|Interview 2|Candidate 1|Pending
Interview 3|Interview 5|Candidate 2|Pending
Interview 4|interview 6|Candidate 3|Clear
and so on

In case of the latter you can unpivot aaa and bbb. This wil create a table where you will find the interviewer in one table and the interviews the interviewer has conducted in a values table. This will however make it so that the candidates where interviewed by both interviewers. I do not know if this is what you want. You can work around this, but for that we would need more information and a more clear question.

Both ways described above would let you create a filter for interviewer and thus let you calculate whatever you want for the corresponding interviewer.

hope this helps

Answer 2

Are you 100% married to the idea of keeping everything in one table? There are some advantages to the approach of creating separate tables for the interviewers, candidates, and possibly the interview status.

However, let's assume that you prefer to keep everything in one table. There's actually no need to unpivot columns to solve what you're looking for.

I recommend using a tidy data approach and creating one column for each variable. In this case the variables are the candidate, the interviewer, the date of the interview, which interview it was, and what the interview status is. Personally I would make the interview status a calculated column either directly in the query or after the table loads and using DAX.

This is how I would approach it - first make a duplicate of the original query. Drop the interview status column for now in both queries.

In your original query, also get rid of the columns for the interviewer and interview date for the second interview. You should have three columns left in the original query - candidate, interviewer 1, and interview 1 date. Create a new column for the interview stage. Populate it with something like "1" or "First".

In your duplicate query keep the information for candidate, interviewer 2, and interview 2 date. Get rid of interviewer 1 and interview 1 date. You should have three columns, candidate, interviewer 2, and interview 2 date. Create a new column for the interview stage. Populate it with something like "2" or "Second".

In both queries change the column names so they're the same in both queries. I recommend simply dropping the 1 or 2 from the interviewer and interview date columns.

Append the two queries together. You should now have one table with four columns: candidate, interviewer, interview date, and interview stage. Since your primary interest is in the interviewer, move that column to the far left. Sort by the interviewer first (ascending or descending by whichever works better for you), then by the candidate ascending or descending, and then by the date in ascending order. Add an index column and either leave it at the end or move it to the far left as you choose. It doesn't matter if you start at 0 or 1 on the index column.

At this point you can either load the table or try to create a status column using whatever logic determines pending vs cleared or other statuses you might have. Personally I find it easier to create columns for that type of logic using DAX but it may be easier to do it in the query depending on how complex the logic is.

Once you have that calculated column for the status you should have everything you need to generate the visuals for what you want to see. The index column is there to give you more options with how you approach the status column. It also gives you a way to put the table in the exact order you had it in the query prior to load. As I'm sure you've noticed when looking at your tables in the datasheet view after load, the rows probably aren't in the same order that they were in the query. Also, you can't sort on more than one column at a time in the datasheet view. Sorting by the index column takes care of both those concerns.

If you do the status column in DAX, you will probably want to look at the EARLIER function if you're not already familiar with it.