Calculated Columns

[abukapsoun]

Hi Dears,

I have the following case please: 

Column1 Column2

X               Active

Y               Obselete

Z               Active

How can I create a new (column or table?) that returns the value of Column1 with a criteria Column2 = Active ? so only X and Z are returned. 

Moreover how can I compare two columns and return the differences between both into a new column/ table?

Many thanks,

[v-yuezhe-msft]

@abukapsoun,

Select “Active” for Column2 in Visual level filters, and right click on your table, select "New Column" and apply the following formula.

Column = Table1[Column4]-Table1[Column3]

Regards,
Lydia

[Greg_Deckler]

Well, you could use CALCULATETABLE and feed it a filter for Active. Or you could just create a table visualization and use a slicer.

To create a new column with the difference between two other columns, just use subtraction?

[abukapsoun]

@Greg_Deckler I tried the substraction but it didn't work. In fact, i need to return the difference in rows. 

for example if 

Column A

X

Y

Z

Column B

Y

Z

I want to return a new column with value Z

thanks

[Greg_Deckler]

Now I'm really confused as that doesn't seem to be the same problem that was originally stated.

[abukapsoun]

@Greg_Deckler Sorry it seems I have explained myself wrong.

for the first problem it is clear about the calculatedtable with filter = Active 

but now I am stuck with returning the differences between the two column. What I want to reach is the following. I have a column that is being updated dynamically. I have duplicated this table and disabled the "load on refresh" then I want to compare both and return what has been newly added compared to that static column which is not refreshing anymore.

[Greg_Deckler · Accepted solution]

I would look at using NATURALOUTERJOIN if you are going to do this in DAX:

https://msdn.microsoft.com/en-us/library/dn802527.aspx

You could also use a Merge Query in the Query Editor and accomplish this as well, just change the default join type.