topic Create calculated column in Live connection in DAX Commands and Tips

Create calculated column in Live connection

Arioli_Chezhian — Tue, 29 Aug 2023 08:51:28 GMT

Hello All,

I have 2 tables, Table 1 is live connection(Using existing dataset) and Table 2 is import file. The Employee ID present in import file should be shown as "Flag - Internal", and for other records should be shown as "External" or "Blank".

We have to bring that Flag column from table 2 to table 1. Using RELATED function we can achieve this but since Table 1 is live connection unable to create Calculate column and Calculate Table.
Using relationship between these 2 table if we directly drag and drop the Flag column in a table visual, data will filtered out where Flag = Internal. Flag = External won'tbe appeared in the visual. I believe it should be handled in Measure.

Please advice how to achieve this measure.

Re: Create calculated column in Live connection

Mahesh0016 — Tue, 29 Aug 2023 09:09:55 GMT

Arioli_Chezhian Table 1 is your fact table and Table 2 is the Dimension Table, Right?

Re: Create calculated column in Live connection

Arioli_Chezhian — Tue, 29 Aug 2023 09:42:10 GMT

Mahesh0016 yes, that's correct.

Re: Create calculated column in Live connection

Mahesh0016 — Tue, 29 Aug 2023 10:27:03 GMT

Arioli_Chezhian I hope this helps you. Thank you.

>Create Following Measures :
Tbl1 =
SUM ( 'Table Workspace'[Emp ID] )
#################################
Tbl2 =
SUMX ( 'Table Flag', [Tbl1] )
################################
Flag =
IF ( [Tbl1] = [Tbl2], "Internal", "External" )

Re: Create calculated column in Live connection

Arioli_Chezhian — Tue, 29 Aug 2023 10:18:39 GMT

Mahesh0016
I have a correction in the data. You have done SUM for Emp ID, just for example I gave Emp ID as number but in my case the EMP ID has alphabetics with numerics. (Example: abcd324).
In this case we cannot SUM the column right, as the datatype will stay as TEXT.

How to overcome this?

Re: Create calculated column in Live connection

Mahesh0016 — Tue, 29 Aug 2023 10:24:03 GMT

Arioli_Chezhian okay, I will try and let you know one more thing is your employee ID length is same, right

Re: Create calculated column in Live connection

Mahesh0016 — Tue, 29 Aug 2023 10:31:21 GMT

Arioli_Chezhian I hope this helps you. Thank you.

>Create Following Measures :
Tbl1 =
MAX ( 'Table Workspace'[Emp ID] )
#################################
Tbl2 =
MAXX ( 'Table Flag', [Tbl1] )
################################
Flag =
IF ( [Tbl1] = [Tbl2], "Internal", "External" )

Re: Create calculated column in Live connection

Arioli_Chezhian — Tue, 29 Aug 2023 10:30:16 GMT

Mahesh0016
Yes it's consists of 4 alphabetics continue with 3 numerics (abcd324).

Re: Create calculated column in Live connection

Mahesh0016 — Tue, 29 Aug 2023 10:32:40 GMT

Arioli_Chezhian it's Done Please Check my Solution. Thank You.

Re: Create calculated column in Live connection

Arioli_Chezhian — Tue, 29 Aug 2023 10:51:34 GMT

Mahesh0016 - I tried but when I drag and drop this measure, the visual keeps on loading.

Re: Create calculated column in Live connection

Mahesh0016 — Tue, 29 Aug 2023 11:35:31 GMT

Arioli_Chezhian are you plot columns from the fact table or Dim in visual?

Re: Create calculated column in Live connection

Arioli_Chezhian — Tue, 29 Aug 2023 12:14:31 GMT

Mahesh0016 - I am bringing data from Table 1, which contains all the emp ID. Even in Table 1 it has unique records, no duplicates.

Re: Create calculated column in Live connection

Mahesh0016 — Tue, 29 Aug 2023 12:24:15 GMT

Arioli_Chezhian Please can you Elobrate Table 1 and Table 2 Relationships.
For E.g One To Many, One to One

Re: Create calculated column in Live connection

Arioli_Chezhian — Tue, 29 Aug 2023 12:27:15 GMT

Mahesh0016 - One to One

Re: Create calculated column in Live connection

Mahesh0016 — Tue, 29 Aug 2023 12:51:42 GMT

Arioli_Chezhian Please Try This one.

Flag =

IF (

SELECTEDVALUE ( 'Table 1'[Emp ID] ) = MAX ( 'Table 2'[Emp ID] ),

"Internal",

"External"

)