Forum Discussion
Different dates and categorical values in same rows
- 2 years ago
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).
- 2 years ago
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.
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.