Count amount of occurrence with filter in Power Query

[Anonymous]

So I got the following Calculated Column created with DAX code. The goal is to count how many products each customer has. Each product has an unique ID and we do have a field end date which is filled in if the customer does not use their product anymore.  

Amount of different products in use per customer   =
VAR __Customer = FACT_Sales[Master ID]
VAR __DCountVIN =  CALCULATE(DISTINCTCOUNT(FACT_Sales[uniqueProductID]), ISBLANK(FACT_Sales[End_Date]))
 RETURN
__DCountVIN

This column works, but it takes forever to load. I'm looking to optimize it a bit and was thinking of doing this in Power Query. But I'm struggling in converting the code to Power Query. Is this even possible? Anyone that can support? 

Thanks in advanced! 

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Download example PBIX file

After seeing your data I decided to use another approach rather than the grouping I initially thought of.

Add a Custom Column in Power Query and you can do this with this line of code

= List.Count(List.Distinct(let _CustID = [Customer ID] in Table.SelectRows(#"Changed Type", each [Customer ID] = _CustID)[Unique Product ID]))

How it works:

This stores the Customer ID in a variable calld _CustID

let _CustID = [Customer ID]

This selects the rows in the table from the previous step (#"Changed Type" - you may have to change this to match your query) where the Customer ID = _CustID

Table.SelectRows(#"Changed Type", each [Customer ID] = _CustID)

This returns a list of the distinct Products each Customer uses

List.Distinct(.... , [Unique Product ID])

This counts the number of items in that list

List.Count

You'll notice my results are different to the ones you show in your sample data, but I believe mine are correct.  For example, Customer 1 uses Products A, D and C.  Customer 2 uses B, E, F and G.

Regards

Phil

[Anonymous]

Hi Phil! 

Thanks a lot for your reply and solution!  Indeed your results where different because it did not take in account the records with an end date (which it should do)

I modified your query a bit and added a "end end date = null" which works. 

= List.Count(List.Distinct(let _CustID = [Customer ID] in Table.SelectRows(#"Changed Type", each ([Customer ID] = _CustID) and ([End date product use] = null))
[Unique Product ID]))

Again thanks a lot and I wish you a nice weekend!

Kind regards

Sofian

[PhilipTreacy]

Hi @Anonymous 

You could group by Customer and use Distinct Count as the aggregate for the Product.

Please provide some sample data and I'll show you an example.

Regards

Phil

[Anonymous]

Hi Philip,

Did you have time to have a look into this? I'm a bit stuck and your support would be really great. :😁

[Anonymous]

Hi Philip, 

Here you can find a file with some sample data: https://we.tl/t-vrK1D7DUbG

(For future reference, this is the sample data: 

Customer ID	Sales date	Unique Product ID	End date product use	Amount of different products in use per customer
1	01 January 2020	A	06 January 2020	2
2	02 January 2020	B	07 January 2020	3
3	03 January 2020	C	08 January 2020	1
1	04 January 2020	D		2
2	05 January 2020	E		3
2	06 January 2020	F		3
3	07 January 2020	A		1
5	08 January 2020	B		1
1	09 January 2020	C		2
4	10 January 2020	H	15 January 2020
2	11 January 2020	G		3