Distinct count with filter from another table

Anything related to PowerPivot and DAX Formuale
luuminhvuong93
Posts: 1
Joined: Sun Apr 09, 2017 7:05 pm

Distinct count with filter from another table

Postby luuminhvuong93 » Sun Apr 09, 2017 7:09 pm

Hi all,
I have a situation like in a simplified data in the excel attached.
2 tables: Fact and Plan.
*Fact: showing how much Volume was sold to a customer, by each Saleman. The period in example is Month 1 and 2 , but actually this is from 2015-2016 (24 months).
Note that 1 customer can buy from multiple Salemen.
*Plan: showing which Customer is being assigned to call by which Saleman (SM). This is the current assignment (2017).
Note that 1 Customer can be assigned to multiple Salemen.
Some customer in the fact table (i.e. E, G, H no longer exists)

[u]Question: How to Calculate the: Number of Month (after a certain month, for example here is January) that a customer have volume >0?
(has to be a Calculated Column)

I tried the below formula - it works fine in the example simplified data, but it takes forever to calculate in my actual data (+1,000,000 rows) and froze my laptop. I am new , so I don't know what could be a better way to achieve this result.

Code: Select all

=CALCULATE(DISTINCTCOUNT('Fact'[Month]),'Fact'[Customer]=EARLIER(Plan[Customer]),'Fact'[Volume]>0,'Fact'[Month]>1)


Below is the example data - simplified
https://drive.google.com/file/d/0B7l_L5iVxXj-WE1Rem9SQk9VLTQ/view?usp=sharing

MattAllington
Posts: 873
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Distinct count with filter from another table

Postby MattAllington » Fri Apr 14, 2017 10:19 am

The period in example is Month 1 and 2 , but actually this is from 2015-2016 (24 months).


It would be easier to help you if you resolved this in your test data.

Question: How to Calculate the: Number of Month (after a certain month, for example here is January) that a customer have volume >0?


Are you saying "how many months in a row has the customer purchased something? If not, can you please try to explain what you want again. If this is what you want, how can this be done given that it seems your test data is for 2 years, not months.
Matt Allington is Professional Self Service BI Consultant, Trainer and Author of the book "Learn to Write DAX". You can hire me at http://Exceleratorbi.com.au
http://exceleratorbi.com.au/what-is-power-pivot/
http://xbi.com.au/learndax


Return to “PowerPivot/DAX”

Who is online

Users browsing this forum: Bing [Bot] and 2 guests