Forum Discussion

Snurresprett's avatar
Snurresprett
New Member
2 years ago
Solved

Different dates and categorical values in same rows

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 th...
  • vicky_'s avatar
    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).

  • DataNinja777's avatar
    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.