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!

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:

Please mark as answer if this post helped you

Thank you for your reply,

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 yewgeniy 18 hours 10 minutes ago

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 :-)

______________________________________________

Please mark helpful posts or answers!

• Marked as answer by yewgeniy 14 hours 14 minutes ago

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 yewgeniy 14 hours 17 minutes ago

Hi Mathaeus,

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

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?

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.
Same link.

• Edited by Herbert Seidenberg 10 hours 2 minutes ago
• Marked as answer by yewgeniy 14 minutes ago

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,

Hi Herbert,

thank you! It helps enormously!

Rgds

Yewgeniy