Forum Discussion

DeoYadav's avatar
DeoYadav
Regular Visitor
4 years ago
Solved

Calculated table using DAX

How to get the result as below.

 

EMP_table

  

 

Department_table

 

Result Table

EmpIdNameDepName
1RajIT
2KishorHR
2AbhiFR
4KiranNo Department

 

May be I'm missing the result in this example.

 

DAX
  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    4 years ago

    Hi DeoYadav,

     

    I use selectedcolumns() to do this.

     

    Result Table =
SELECTCOLUMNS (
    'EMP_table',
    "empid", [new EmpID],
    "name", [Name],
    "DepName",
        IF (
            ISBLANK ( RELATED ( Department_table[DepName] ) ),
            "No Department",
            RELATED ( Department_table[DepName] )
        )
)

     

     

    Result:

     


    Pbix in the end you can refer.

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi,

    Your department table had a duplicate id. I will ignore it in my example.

    Here is one way to do this:

    Table 6 = ADDCOLUMNS(EMP_table,"Department",RELATED(Department_table[DepName]))

     


    If you want to differentiate between HR and FR the depID's should be unique.

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi ValtteriN  tables can have multiple ids where we use M:M relationship in our model, correct? so what would be result in the case of M:M, can we get the result as shown in the result table?

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Community Support
    4 years ago

    Hi DeoYadav ,

     

    Can you explain why the second and third rows are the same Empid 2?

     

    If you want get the depName from Department_table, a relationship between these two tables on DepID is needed. Then create the new column in EMP_table via related(Department_table[DepName])

     

    Best Regards

    Community Support Team _ chenwu zhu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi v-chenwuz-msft , "Can you explain why the second and third rows are the same Empid 2?" - this is the result that I want in a calculated table.

       

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Community Support
        4 years ago

        Hi DeoYadav,

         

        I use selectedcolumns() to do this.

         

        Result Table =
SELECTCOLUMNS (
    'EMP_table',
    "empid", [new EmpID],
    "name", [Name],
    "DepName",
        IF (
            ISBLANK ( RELATED ( Department_table[DepName] ) ),
            "No Department",
            RELATED ( Department_table[DepName] )
        )
)

         

         

        Result:

         


        Pbix in the end you can refer.

        Best Regards

        Community Support Team _ chenwu zhu

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.