Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to Solution.
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,
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 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,
when I select 01/02/2019 it shows the correct result
when I select candidate ID it shows the wrong result
1) interview result table did not filter based on candidate selection
2) I have selected C1 candidate in Personal Information table, it wants to drill down to his interview result in drill down page
Data model
I'm new to Power PI, Please help me to solve this issue
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,
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
104 | |
93 | |
64 | |
61 |