locked
DISTINCTCOUNT (RELATED TABLES) RRS feed

  • Question

  • Hi All

    I am trying to do a proper DAX for my calculated column "Rep Loyality".

    "Rep Loyality" should return a number of campaigns (offr_campgn_perd_id) in which representatives (acct_nr) bought any of a company's product. For instance. There are 17 campaigns each year. If a representative with her unique number "acct_nr = 123456" made 20 transactions in any 5 campaigns I should get Rep Loyality = 5 for acct_nr = 123456.

    I have a idea to place "Rep Loyality" calculated column in "Rep Birth date Current Divisi" table where I have unique "acct_nr"

    Part of data model:

    I need to have "Rep Loyality" also for particular year. In this case YEAR =2015.

    YEAR is placed in "Data - Offer Campaign Period" table.

    Till now I have tried with this:

    Rep Loyality =
    CALCULATE (
        DISTINCTCOUNT ( trx_pol[offr_cmpgn_perd_id] );
        ALLEXCEPT ( trx_pol; trx_pol[acct_nr] );
        'Data - Offer Campaign Period'[YEAR] = 2015
    )

    It is not really working as I expect. 

    Returns always "17" for each "acct_nr" 

    17 is a maximum nr of campaigns in a year. Not each "acct_nr" made transaction in all 17 campaigns.

    "Trx_pol" is a table contains all transactions data. (120M rows)

    I hope this is understandable :)

    Any idea how to fix a DAX above?

    Saturday, March 19, 2016 2:40 PM

Answers

All replies

  • Hi Barket,

    In PowerPivot, the related distinct count pattern allows us to apply the distinct count calculation to any column in any table in the data model. You can calculated the number of distinct products bought in a particular period. Please refer to the link below to see the details.
    http://www.daxpatterns.com/distinct-count/

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, March 21, 2016 8:01 AM
  • Thanks Charlie

    But it does not really solve my issue.

    I share with you the full model. 

    My final goal is to be able to know how many representatives are loyal (bought in more than 5 campaigns) and occasional (bought in less than 5 campaigns). Also I need to have a KPI's as Units or gross sales for those groups. So my idea was to have this in calculated column to be able to easily get such information



    Monday, March 21, 2016 10:22 AM
  • Bartek, any luck with this?

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, July 29, 2016 8:27 PM
  • Bartek, you can send a Frown to the Power Pivot team if you run into any more troubles. Thanks!

    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, October 30, 2016 10:20 AM