Calculated table using DAX

[DeoYadav]

How to get the result as below.

EMP_table

  

Department_table

Result Table

EmpId	Name	DepName
1	Raj	IT
2	Kishor	HR
2	Abhi	FR
4	Kiran	No Department

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

[v-chenwuz-msft]

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]

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 · Accepted solution]

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.

[ValtteriN]

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]

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?