• ### Question

• Hi everyone,

I'm trying to find the right approach for my problem & now after many hours need help.

Table with 3 columns:

1) Customer ID (unique value)

2) Payer ID (not unique values), could be the same like customer ID or some other ID

3) Ship to ID (not unique values)

I.e. many unique customer IDs can have same payer ID, or Ship to ID etc.

600001;600001;600001

600002;600001;600002

600003;600003;600003

600004;600004;600003

The challenge is to find a formula (calculated measure), which counts for every customer ID how many times this ID number appears in the other two columns.

The final outcome should be as follows

600001;2;1

600002;0;1

600003;1;2

600004;1;0

Your help would be really appreciated!

Tuesday, May 26, 2015 12:40 PM

• Hey yewgeniy,

check out my demo-file: http://1drv.ms/1cXJP0P

I don't know if you need the expected result in the datamodel or as a measure in Excel so I build both ways :-)

______________________________________________

• Marked as answer by Tuesday, May 26, 2015 5:32 PM
Tuesday, May 26, 2015 1:58 PM
• Excel 2010 with free PowerPivot Add-In.
Compatible with Office 2013 Pro Plus.
With UserRelationship()
http://www.mediafire.com/view/5k2kszsbju7ck6a/05_26_15.xlsx

• Marked as answer by Tuesday, May 26, 2015 5:29 PM
Tuesday, May 26, 2015 3:36 PM
• Excel 2010 PowerPivot
Count() is for numbers, CountA() is for text.
See DAX language library at:
https://msdn.microsoft.com/en-us/library/ee634396.aspx

Added my text example and Woroch's excellent method with Earlier().
There are oodles of other ways.

Tuesday, May 26, 2015 9:42 PM

### All replies

• By the way that you are talking about calculated measure I am assuming you are using some cube and so some sql table. In that case you can create a view like this below:

```create table forum (Fr int, Sc Int, Th int)

insert into forum values (600001,600001,600001),(600002,600001,600002), (600003,600003,600003), (600004,600004,600003)

with cte as ( select count(fr) as frsc, sc from forum group by SC),
cte2 as (select count(fr) as frth,th from forum group by Th)
select Fr, isnull(frsc,0),isnull(frth,0) from forum f left join cte c on f.Fr=c.Sc left join cte2 c2 on f.Fr=c2.th  ```

Output:

Tuesday, May 26, 2015 1:05 PM

to be honestly I'm far away regarding the knowledge of sql code.

I rather have to accomplish it with powerpivot, with 18.000 rows table. Any ideay how to achieve this within powerpivot?

In Excel I could achieve this with simple countif function, but not in powerpivot :)
• Edited by Tuesday, May 26, 2015 1:36 PM
Tuesday, May 26, 2015 1:13 PM
• Hey yewgeniy,

check out my demo-file: http://1drv.ms/1cXJP0P

I don't know if you need the expected result in the datamodel or as a measure in Excel so I build both ways :-)

______________________________________________

• Marked as answer by Tuesday, May 26, 2015 5:32 PM
Tuesday, May 26, 2015 1:58 PM
• Excel 2010 with free PowerPivot Add-In.
Compatible with Office 2013 Pro Plus.
With UserRelationship()
http://www.mediafire.com/view/5k2kszsbju7ck6a/05_26_15.xlsx

• Marked as answer by Tuesday, May 26, 2015 5:29 PM
Tuesday, May 26, 2015 3:36 PM
• Hi Mathaeus,

it looks very well! How did you do it? I can not see it in my Excel 2010.

Tuesday, May 26, 2015 5:07 PM
• Thanks Herbert,

by applying the function "count" error message pops up for not able to calculate it as string...

Is there any alternative for strings?

Tuesday, May 26, 2015 5:32 PM
• Excel 2010 PowerPivot
Count() is for numbers, CountA() is for text.
See DAX language library at:
https://msdn.microsoft.com/en-us/library/ee634396.aspx

Added my text example and Woroch's excellent method with Earlier().
There are oodles of other ways.

Tuesday, May 26, 2015 9:42 PM
• Hi Mathaeus,

it looks very well! How did you do it? I can not see it in my Excel 2010.

Hi Yewgeniy,

We can use IF function to check if PayerID value is equal to CustomerID value. Similary, use IF function to check if ShipID value is equal to CustomerID value.
=IF([PayerID]=[CustomerID],1,0)
=IF([ShipID]=[CustomerID],1,0)

Regards,

Charlie Liao
TechNet Community Support

Wednesday, May 27, 2015 3:03 AM
• Hi Herbert,

thank you! It helps enormously!

Rgds

Yewgeniy

Wednesday, May 27, 2015 7:35 AM