Forum Discussion
Data model Creation
Hi ALL
I have two tables like below
Table 1
Table 2
By using these two tables How can I create a data model like below image?
Final Data Model
Data:
Interview Registration:
| Registration Date | Candidate ID | Name | Address | Phone | Experience | Department | |
| 1/1/2019 | C1 | A | XXXX | XDE | 987654 | 2 | kkk |
| 1/1/2019 | C2 | B | XX | KLMJ | 3456 | 1 | LMN |
| 1/2/2019 | C3 | C | XXX | mnMJ | 356 | 4 | JL |
| 1/2/2019 | C4 | D | XXXCC | lMJ | 632 | 5 | JL |
Interview Round:
| Interview Date | Candidate ID | Round | Interviewer | Result |
| 1/1/2019 | C1 | Tech1 | KL | Selected |
| 1/2/2019 | C1 | Tech2 | ML | Selected |
| 1/3/2019 | C1 | HR | KJL | Selected |
| 1/4/2019 | C1 | Final | AAA | Selected |
| 1/2/2019 | C2 | Tech1 | KL | Selected |
| 1/2/2019 | C2 | Tech2 | ML | Hold |
| 1/3/2019 | C3 | Tech1 | KL | Select |
| 1/4/2019 | C3 | Tech2 | ML | Rejected |
Hi Anonymous,
I would suggest you create a new table in your scenario. The slicer Candidate will be from it.
CandidateIDs = DISTINCT ( UNION ( VALUES ( Table1[Candidate ID] ), VALUES ( Table2[Candidate ID] ) ) )
Best Regards,
5 Replies
Hi Anonymous,
Please download the solution from the attachment.
Current Round = VAR allRound = CALCULATETABLE ( VALUES ( Table2[Round] ) ) RETURN SWITCH ( TRUE (), "Final" IN allRound, "Final", "HR" IN allRound, "HR", "Tech2" IN allRound, "Tech2", "Tech1" IN allRound, "Tech1", BLANK () )
Current Result = LOOKUPVALUE ( Table2[Result], Table2[Round], [Current Round], Table2[Candidate ID], [Candidate ID] )
Tech1 = LOOKUPVALUE ( Table2[Result], Table2[Candidate ID], [Candidate ID], Table2[Round], "Tech1" )
Tech2 = LOOKUPVALUE ( Table2[Result], Table2[Candidate ID], [Candidate ID], Table2[Round], "Tech2" )
HR = LOOKUPVALUE ( Table2[Result], Table2[Candidate ID], [Candidate ID], Table2[Round], "HR" )
Final = LOOKUPVALUE ( Table2[Result], Table2[Candidate ID], [Candidate ID], Table2[Round], "Final" )
Best Regards,
Thanks v-jiascu-msft
in the data model, I want to update Table 2 (Interview Round) like below image
I want to add the registered date in this table, then only I can complete my requirement
Requirement
- How many candidates registered on the selected date? and drill down to their personal information and interview result
- How many candidates attend the interview on the selected date? and drill down to their personal information and interview result
How can i achieve this logic? please give some idea to complete this requirement
Hi Anonymous,
These data is in Table 1. Why would you like to add them in Table 2? I would suggest you keep the model simple and clear.
You need to add a date table. Then you can solve the two questions using measures.
Best Regards,