topic Transpose data from one file to another if the Data exists - i think it's a calculated column? in DAX Commands and Tips

Transpose data from one file to another if the Data exists - i think it's a calculated column?

sharpedogs — Sun, 02 Feb 2020 03:19:04 GMT

I have two files as per the Tables below.

Table 1 is my master file with the full set of devices and the current confidence level.

Table 2 is the smaller subset of devices with an updated status in the confidence level column.

I want to take the New status in the Confidence Level from Table 2 and transpose it to the correct devices in Table 1.

I think I might have to do a new calculated column? I'm just not sure of the DAX.

Table 1

Device Name	Confidence Level
AAA	High
BBB	High
CCC	High

Table 2

Device Name
AAA	Medium

End State would look like

Device Name	Confidence Level
AAA	Medium
BBB	High
CCC	High

Re: Transpose data from one file to another if the Data exists - i think it's a calculated column?

Anonymous — Sun, 02 Feb 2020 03:33:23 GMT

I consider your tables are connected in with many to one or one to many relationship.

You have to simply drag device name from master table and status from second table.

Incase u want new column in master table only and if there is join on device name.

Then use below dax for column in first table

New column= related(table2[status])

Thanks and regards,
Pravin Wattamwar

If it resolves your problem mark it as a solution and give Kudos.

Re: Transpose data from one file to another if the Data exists - i think it's a calculated column?

sharpedogs — Sun, 02 Feb 2020 03:47:46 GMT

Hi,

There is a relationship, it's a 1 to 1 on the device name.

The DAX you gave is good, the issue it leaves me with is that the calculated coumn now has a bunch of blanks. How do I add onto the DAX so that when it moves over the few updated values it then fills in the rest of the column the the old values?

Re: Transpose data from one file to another if the Data exists - i think it's a calculated column?

Anonymous — Sun, 02 Feb 2020 03:52:24 GMT

Col1= related(table2,[status])

Col2=if(isblank(table1[col1]),table1[status],table1[col1])

If it resolves your problem mark it as a solution and give Kudos.

Re: Transpose data from one file to another if the Data exists - i think it's a calculated column?

sharpedogs — Sun, 02 Feb 2020 04:26:24 GMT

Really appriciate the support late on a Saturday night.... it worked perfectly!!!