Distinctcount (related tables)

[Gordonik]

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:

RepLoyality=
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?

 

[Matt Allington]

I hope this is understandable
Any idea how to fix a DAX above?

I can see you have tried hard to give lots of information here, but unfortunately no it is not understandable like this. Power Pivot is an integrated system of tables, relationships and formulas that all work together as a whole. Just looking at a sub set of the tables like you have shown here is not enough information for anyone to provide help.

Without even seeing the rest of the data model, I can assume that there are some things that you may need to look at.
1. You don't seem to have your table structure optimised. Read about that here => The Optimal Shape for Power Pivot Data - Excelerator BI
2. It looks like you are relying too much on Calculated Columns. Read about that here => 5 common mistakes made by self taught DAX students - PowerPivotPro

From my experience, people sometimes ask "how can I fix this one thing", but upon a full review of the current situation what they really need is to stop, get some foundational learning and then start again applying better practices. I can't say for sure this applies in this case because I haven't seen the bigger picture, but it is a possibility. If you could even post a full picture of your data model I could give you an updated opinion.

 

[Gordonik]

Thanks Matt

I know this model is imperfect. It is still growing and changing.
I do not show calculated fields on the screen. Just columns only.
Here is the full model:

I am not fully sure the best way is to do a calculated column in this case. Can be a measure.

 

[Gordonik]

At the end I want to use Rep Loyalty to get a number of loyal representatives (those who bought in more than 5 campaigns) and occasional representatives (those who bought in less than 5 campaigns)

 

[Matt Allington]

I realise you know your data better than anyone casually looking at this. It helps that I can see all the tables but I can't really understand the data model so it is difficult for me to advise. Some general comments I can give
1. The shape of your data from your source system is normally not the best for power pivot
2. Try to give your tables meaningful business names (eg not trx_pol). Same with column names.
3. There seem to be a lot of columns in the data tables, particularly maps_data. It looks like you have loaded what you have.

Can an you take a step back and describe
1. The business context
2. What you are trying to measure/ report
3. What data you have to support 1 and 2

 

[Gordonik]

Matt I apreciate your help
Table’s names are source data names. I know it is not understandable from business point of view. I will adjust it in future
My PowerPivot in Excel 2013 has a disadvantage that if I change a table or column name all the referring measures collapse. It is solve in PowerBi but not in excel I guess.
There are a lot of columns in Maps data. Totally agree. Some of them are not necessary at this moment but can be used in future. This table is low memory usage. Contains not many rows.
The main and most important table is trx_pol. It contains all transaction data. Let’s go through columns:
offr_cmpgn_perd_id – period id (year + campaign no)
acct_nr – unique representative’s number
opertnl_sgmtn_typ_key – representative segment. Available only in this table
prmry_prfl_cd – product unique code. In other tables it is called Marketing concept no. Here is a mess in company’s internal source data names of attributes
gross_sales, cust_price_sales, units, Commission. – KPI’s
Billing_Line_Nr2– is a key to connect to Maps data

Category and Offer Campaign Period – related column. Previously I had used Category from ‘Data - GSAM - Product Hierarchy’ table and Offer Campaign Period from ‘Data - Offer Campaign Period’ table.
But from query point of view it was hard to compute. Each change in pivot takes hours. Now it works faster, but memory usage grew up.

Data - Offer Campaign Period – custom calendar table
Rep Birth date Current Divisi – table with unique representative characteristic as birth’s day (brth_dt)
Or division (div_no). There is also duplicated (related) Age Group column. Placed here also due to query performance reasons

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

 

[Matt Allington]

Category and Offer Campaign Period – related column. Previously I had used Category from ‘Data - GSAM - Product Hierarchy’ table and Offer Campaign Period from ‘Data - Offer Campaign Period’ table.
But from query point of view it was hard to compute. Each change in pivot takes hours. Now it works faster, but memory usage grew up.

really? Something doesn't sound right. How many rows and columns in the relevant tables?

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

what is the definition of "bought"? If I guess correctly, it is gross_sales >0

it looks like a many to many problem. Timing is good. Read my blog post from today. http://exceleratorbi.com.au/many-many-relationships-dax-explained/

 

[Gordonik]

Matt, very good post!

Ya, it doesn't sound right. Number of rows by table:
trx_pol - 130M
maps_data - 116k
Rep Birth date, Current Divisi - 1,7M
Data - GSAM - Product Hierarchy - 5k
Data - GSAM - KPI's - 123k
Data - Platfroms Mapping - 0,6k
Billing Line Nr2 - 6,6k

All rows in trx_pol are with sales >0. So i do not focus on sales but on distinct number of campaign periods for each representatives. This is equal to a number of campaigns in which representative made transaction, so bought product - DISTINCTCOUNT ( trx_pol[offr_cmpgn_perd_id])

I agree. it must be sth with m2m relations.