Data model Creation

[Anonymous]

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	Email	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

[v-jiascu-msft]

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,

[Anonymous]

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

1. How many candidates registered on the selected date? and drill down to their personal information and interview result 
2. 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 

[v-jiascu-msft]

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,

[Anonymous]

@v-jiascu-msft

pbix file

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 

[v-jiascu-msft · Accepted 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,