Dax measure for two new columns in a table.

[ROG]

Hi guys,

I want to have two new columns on the table below.
One where it would tell me when the customer changes the product

Secondly "1" if the product is differente bettween the columns (First_Product and CurrentProduct) and "0" if they have the same product.

I'm new to PBI and still learning dax, so I much appreciate your help. 🙂

Thanks!

[Anonymous · Accepted solution]

Hi @ROG ,

Please select any one field of your table and navigate to "Column tools" ribbon. Click "New column" and type the formula as below to create a calculated column (see attachment)...

Tutorial: Create calculated columns in Power BI Desktop

Column = IF([First_Product]=[CurrentProduct],0,1)

In addition, the below blogs explain the difference between calculated column vs measure, please review them for more details...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

And how can we get the value for when the customer changes the product? What's the calculation logic? Is there any date field in your table? Could you please provide more details on it? Thank you.

Best Regards

[Greg_Deckler]

@ROG For the second, it should be something like:

Column = IF([First_Product] = [CurrentProduct],0,1)

For the first one, not really enough to go on. You would need more data, like a date or index in order to figure out "when". So if you had something like that you could do this:

Column = 
  VAR __Customer = [Cust_ID]
RETURN
  MINX(FILTER('Table',[Cust_ID] = __Customer && [First_Product] <> [CurrentProduct]),[Date])

[ROG]

Thanks a million @Greg_Deckler 

For this one, the highlighted needs to be a measure?  The columns from my table are just fields from different tables (Cust_ID and First_Product come from the table ProductAcquisition, and CurrentProduct somes from the table CustomerType. I tried to create a measure but it's not working. Se below the error I get. 

I could create caluclated columns, but I think because they're from different tables, I need add something else to the mesause below?

If both need to be measures, how I can create a measure that would copy the values from a field?

Thanks,

Renata

[Greg_Deckler]

@ROG Ah, you said columns so I was thinking columns in your dataset versus columns in a table visual. If you need measures then you will need to wrap the column references in an aggregation like MAX or MIN, etc.

[ROG]

Hey @Greg_Deckler I want to have two new columns in that table, and not measure. I do not want to agregate them.

One column giving me 1 or 0 as a result and another column stating when the product was changed. 
As per your example, I added the columns from the table from the dataset, but that won't work.

[CUSTOMER_CODE] comer from another table.

[Greg_Deckler]

@ROG Let's back up because I feel like I am getting bits and pieces of a problem and not the whole story. If one of the columns comes from another table then you could possibly use RELATED or LOOKUPVALUE or MAXX(FILTER(...),...) to grab the value. But, probably easier if you provide all the information.

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.