locked
countrows, please help! RRS feed

  • 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

Answers

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


    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:

    Please mark as answer if this post helped you

    Tuesday, May 26, 2015 1:05 PM
  • 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 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 :-)

    ______________________________________________

    Please mark helpful posts or answers!

    • Marked as answer by yewgeniy 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 yewgeniy 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.
    Same link.


    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