Forum Discussion
Count amount of occurrence with filter in Power Query
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
__DCountVINThis 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!
Hi Anonymous
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.CountYou'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
5 Replies
Hi Anonymous
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.CountYou'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
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
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
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 customer1 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