Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I am trying to calculate Number of members, who have more than one product, each year for 2018, 2017, 2016, 2015, 2014
The problem is, each product is only owned for a limited amount of time. The member might own product A from 2014 to 2016 own product B from 2015 to 2017. This would mean that the member owned 1 product in 2014, 2 products in 2015, 2 products in 2016 and 1 productd in 2017.
I think I need to nest a Select statement with a nested dax - but I am not sure how. I can count the ActiveProducts with the following statement
ActiveProducts = CALCULATE(COUNTROWS('ProductsOwnedTable'),
FILTER('ProductsOwnedTable', ('ProductsOwnedTable[Start] <= LASTDATE('Date Table'[Date])
&& 'ProductsOwnedTable'[End]>= FIRSTDATE('Date Table'[Date]))))
I can add an IF to determine if the member had more than 1
But how do I get the measure to itarate this for every single member?
I tried to make a summarized table with SUMMARIZE('ProductsOwnedTable','ProductsOwnedTable'[MemberID],"Owned",[ActiveProducts]) but it just captured that total number of rows per member rather than using the date range.
Here's a sample of data
MemberIDProductStartEnd
11 | A | 05/05/2015 | 20/05/2016 |
11 | C | 05/05/2015 | 20/05/2016 |
11 | B | 28/08/2017 | 31/10/2017 |
12 | A | 05/05/2015 | 05/11/2015 |
12 | C | 05/11/2017 | 21/08/2018 |
13 | A | 15/12/2017 | 10/07/2018 |
13 | B | 15/12/2017 | 18/01/2021 |
13 | D | 01/01/2018 | 14/01/2018 |
14 | A | 15/12/2017 | 26/06/2018 |
15 | B | 15/12/2017 | 17/09/2020 |
15 | C | 15/12/2017 | 17/09/2020 |
The result for this sample should be:
Year | 2015 | 2016 | 2017 | 2018 | 2019 |
Total Members with more than one product | 1 | 1 | 2 | 2 | 1 |
Thanks
Solved! Go to Solution.
Step # 2 Now you can use following MEASURE in the New Table to get the desired results
Total Members with more than 1 = COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', 'Table'[MemberID], 'Table'[Year], "mycount", COUNT ( 'Table'[Product] ) ), [mycount] > 1 ) )
I like this formula. It builds on the table created from step 1 of the previous solution. The big advantage of this formula is that I can now visualize the data for ANY time period - month, quarter, year etc instead of being limited to just years.
Thank you
I like this formula. It builds on the table created from step 1 of the previous solution. The big advantage of this formula is that I can now visualize the data for ANY time period - month, quarter, year etc instead of being limited to just years.
Thank you
Hi @kmclorg,
You are welcome. If my reply helped, please mark it as Answer.
Thank you so much! This worked perfectly and I would never have figured it out without your help. The written stuff was clear and the sample file really helped a lot. Since my records span over 125 years, the table I created was almost 8 million records.
So I added a filter when creating the table to limit all start and end dates for just the period that I am interested in
Filter(SELECTCOLUMNS(ProductsTable,"ID",[MemberID],"Product",[Policy Type],"End",if(Year([End])>2018,Date(2019,1,1),[End]),"Start",if(Year([Start])<2014,Date(2013,1,1),[Start])),[End]>=Date(2014,1,1)). This brought the table down to just 700,000 records.
Thanks again for the time and energy you took to help out.
Here are the steps
Step#1: Create a new calculated Table.....from Modelling Tab>>>NEW TABLE
Table = GENERATE ( TableName, GENERATESERIES ( YEAR ( TableName[Start] ), YEAR ( TableName[End] ) ) )
This will create a new Column "Values" representing YEARS against each member
Rename this Column to YEARS
Step # 2 Now you can use following MEASURE in the New Table to get the desired results
Total Members with more than 1 = COUNTROWS ( FILTER ( SUMMARIZE ( 'Table', 'Table'[MemberID], 'Table'[Year], "mycount", COUNT ( 'Table'[Product] ) ), [mycount] > 1 ) )
Hi Zubair,
I found your Dax very helpful.
My doubt is extension to this.
Any ways we are showing count of customers where one have more than one product.
How to display values as zero if a customer has only one product
Regards,
Narasimha
Hi what should I do if I want to use the DISTINCTCOUNT function on a column of the summarized table?
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |