Different dates and categorical values in same rows

[Snurresprett]

Hi,

I'm currently trying to solve an issue that I've got stuck at. I'm having data from a Sharepoint List where each row is a candidate applying for a job, and most of the candidates has to go through three different interviews. Each row as plenty of other attributes as well, but this is were I got stuck.

The data looks like:

Candidate Name	Interview 1#  Date	Interview 1#  Name	Interview 2#  Date	Interview 2#  Name	Interview 3#  Date	Interview 3#  Name
Candidate #1	2024-01-01	Adam	2024-02-01	Anna	2024-03-01	Anna
Candidate #2	2024-01-05	Anthony	2024-02-05	Anthony	2024-03-05	Adam
Candidate #3	2024-01-11	Anna	2024-02-11	Adam	2024-03-11	Anthony

And I would like to build a matrix sums the interviews per interviewer, per month that they were actually held, like this:

No. of interviews	January 24'	February 24'	...
Adam	2	3	...
Anthony	1	1	......
Anna	3	3	....

I've searched the web for several hours, and trying to seperating them into different tables and and relate date 1#, 2# and 3# to a separate date table and sum the values to it, but I'm really stuck on what kind of direction I should go to try and solve it.

As you might have realized, I'm realtively new to PBI but also very interested in learning, so all comments and tips are more than welcome!

[vicky_ · Accepted solution]

Ideally, you'd be able to get your data in a format similar to below: 

Candidate	Interview Date	Interview Name
Candidate #1	1/01/2024	Adam
Candidate #2	5/01/2024	Anthony
Candidate #3	11/01/2024	Anna
Candidate #1	1/02/2024	Anna
Candidate #2	5/02/2024	Anthony
Candidate #3	11/02/2024	Adam
Candidate #1	1/03/2024	Anna
Candidate #2	5/03/2024	Adam
Candidate #3	11/03/2024	Anthony

From which you can easily get your desired table since there's only one date column. 

If you can't do that, then you can do so with dax - create the 1:M relationships between your date table and each of the dates (but leave 2 of them inactive; note the dotted lines):

Then create a measure like the following:

No Interviews = COUNTROWS('Table') + CALCULATE(COUNTROWS('Table'), USERELATIONSHIP('date table'[Date], 'Table'[Interview 2#  Date])) + CALCULATE(COUNTROWS('Table'), USERELATIONSHIP('date table'[Date], 'Table'[Interview 3#  Date]))

The first COUNTROWS('Table') will use date 1 (since it's the one that's active in my table), the 2nd will use date 2 and the 3rd will use date 3. You can use a similar method to get the Interviewer names on the rows (i.e create a table with all of the unique names of the interviewers and then use calculate parameters to force your table to use the inactive relationships).

[Snurresprett]

@DataNinja777 @vicky_ Wow, thank you very much for detailed explainations! I was able to elaborate your solutions to fit the other attributes in the dataset as well! New insights and much joy came out of this! I wish you both all the best!

[DataNinja777 · Accepted solution]

Hi @Snurresprett ,

You can typically use Power Query's Unpivot function to achieve the unpivoted data that @vicky_ mentions.

After using the Unpivot Other Columns function in Power Query, you can use DAX to consolidate the names of the interviewers into one column instead of three columns.

Then, you can create a relationship between your unpivoted fact table and the calendar table.

The resultant output from your sample data of three lines is shown below:

I have attached a sample PBIX file.

[vicky_ · Accepted solution]

Ideally, you'd be able to get your data in a format similar to below: 

Candidate	Interview Date	Interview Name
Candidate #1	1/01/2024	Adam
Candidate #2	5/01/2024	Anthony
Candidate #3	11/01/2024	Anna
Candidate #1	1/02/2024	Anna
Candidate #2	5/02/2024	Anthony
Candidate #3	11/02/2024	Adam
Candidate #1	1/03/2024	Anna
Candidate #2	5/03/2024	Adam
Candidate #3	11/03/2024	Anthony

From which you can easily get your desired table since there's only one date column. 

If you can't do that, then you can do so with dax - create the 1:M relationships between your date table and each of the dates (but leave 2 of them inactive; note the dotted lines):

Then create a measure like the following:

No Interviews = COUNTROWS('Table') + CALCULATE(COUNTROWS('Table'), USERELATIONSHIP('date table'[Date], 'Table'[Interview 2#  Date])) + CALCULATE(COUNTROWS('Table'), USERELATIONSHIP('date table'[Date], 'Table'[Interview 3#  Date]))

The first COUNTROWS('Table') will use date 1 (since it's the one that's active in my table), the 2nd will use date 2 and the 3rd will use date 3. You can use a similar method to get the Interviewer names on the rows (i.e create a table with all of the unique names of the interviewers and then use calculate parameters to force your table to use the inactive relationships).