cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Itay Regular Visitor
Regular Visitor

New Computed Column

Hey,

I'm trying to add a new computed column, that will help me to indicate DISTINCT CustID.

For the example: the new coulmn will bring me "1" on lines 1, 2 and 5. if ill sum it up, ill get 3 uniqe customers.

 

 

Thanks!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: New Computed Column

@Itay  - You could do the following steps:

1. Order by Customer.

2. Add an index column starting from 0, and another starting from 1.

3. Merge the table with itself, joining the one index column to the other. This way, you can retrieve the previous customer.

4. Add a Custom Column that returns 0 if the current and previous customers are the same, otherwise 1.

 

Using an Excel formula (if the source is a spreadsheet) is easier:

1. Order by Customer.

2. Add a new column, with the following formula (Assumes that CustomerID is in Column A):

=IF(A2=A1,0,1)

Hope this helps,

Nathan

View solution in original post

7 REPLIES 7
Super User
Super User

Re: New Computed Column

@Itay -

You could use DISTINCTCOUNT function in a Measure to get the distinct count. That way, you would always get the correct count, regardless of the filter context. If you have a one in some rows and not others, the "1" row may be filtered out in some situations, producing a wrong result.

 

Distinct Customers = DISTINCTCOUNT('Sales'[CustID])

Hope this helps,

Nathan

Itay Regular Visitor
Regular Visitor

Re: New Computed Column

Hey @natelpeterson Thanks, I'm using PowerQuery through the excel and not through the desktop app.

 

i'm aware about the wrong result the may be, but im still need it on this way. Thanks!

Highlighted
Super User
Super User

Re: New Computed Column

@Itay  - You could do the following steps:

1. Order by Customer.

2. Add an index column starting from 0, and another starting from 1.

3. Merge the table with itself, joining the one index column to the other. This way, you can retrieve the previous customer.

4. Add a Custom Column that returns 0 if the current and previous customers are the same, otherwise 1.

 

Using an Excel formula (if the source is a spreadsheet) is easier:

1. Order by Customer.

2. Add a new column, with the following formula (Assumes that CustomerID is in Column A):

=IF(A2=A1,0,1)

Hope this helps,

Nathan

View solution in original post

Itay Regular Visitor
Regular Visitor

Re: New Computed Column

Thanks @natelpeterson ,

 

I think it will help and i'm almost there.


At step 2 (i'm using PowerQuery) - you meant a column with ascending series (starting from 0) or all the column be with "0"?

 

EDIT:

Got you about the index coulmn, but which coulmn I need retrive after I merged?

 

 

Thanks!

Super User
Super User

Re: New Computed Column

@Itay - Please refer to the following screenshot:

Add Column - Index.png

EDIT: The column you need is CustID, so you can compare the current row with the previous row.

Itay Regular Visitor
Regular Visitor

Re: New Computed Column

Thanks @natelpeterson , I edited my previous reply above.

Itay Regular Visitor
Regular Visitor

Re: New Computed Column


@natelpeterson wrote:

@Itay  - You could do the following steps:

1. Order by Customer.

2. Add an index column starting from 0, and another starting from 1.

3. Merge the table with itself, joining the one index column to the other. This way, you can retrieve the previous customer.

4. Add a Custom Column that returns 0 if the current and previous customers are the same, otherwise 1.

 

Using an Excel formula (if the source is a spreadsheet) is easier:

1. Order by Customer.

2. Add a new column, with the following formula (Assumes that CustomerID is in Column A):

=IF(A2=A1,0,1)

Hope this helps,

Nathan


Finally succeed, Thanks so much!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 280 members 3,033 guests
Please welcome our newest community members: