topic Re: Calculated table using DAX in DAX Commands and Tips

Calculated table using DAX

DeoYadav — Wed, 10 Aug 2022 19:55:29 GMT

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.

Re: Calculated table using DAX

ValtteriN — Wed, 10 Aug 2022 20:37:24 GMT

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/

Re: Calculated table using DAX

v-chenwuz-msft — Tue, 16 Aug 2022 09:26:52 GMT

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.

Re: Calculated table using DAX

Anonymous — Tue, 16 Aug 2022 09:47:20 GMT

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.

Re: Calculated table using DAX

Anonymous — Tue, 16 Aug 2022 09:51:42 GMT

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?

Re: Calculated table using DAX

v-chenwuz-msft — Wed, 17 Aug 2022 02:27:43 GMT

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.