Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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] ) )
)

    Data-model-Creation2

    Data-model-Creation3

     

     

    Best Regards,

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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"
)

Data-model-Creation

 

Best Regards,

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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