cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

DAX to Count number Members who have >1 Products owned within specific date range

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

11A05/05/201520/05/2016
11C05/05/201520/05/2016
11B28/08/201731/10/2017
12A05/05/201505/11/2015
12C05/11/201721/08/2018
13A15/12/201710/07/2018
13B15/12/201718/01/2021
13D01/01/201814/01/2018
14A15/12/201726/06/2018
15B15/12/201717/09/2020
15C15/12/201717/09/2020

 

The result for this sample should be:

Year20152016201720182019
Total Members with more than one product11221

 

Thanks

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User I
Super User I

Hi @kmclorg

 

See the attached file here

With your sample data and solution

 

007.png

 

Regards,
Zubair


View solution in original post

Highlighted

@kmclorg

 

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
    )
)
Regards,
Zubair


View solution in original post

Highlighted

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

View solution in original post

9 REPLIES 9
Highlighted
Super User I
Super User I

Hi @kmclorg

 

See the attached file here

With your sample data and solution

 

007.png

 

Regards,
Zubair


View solution in original post

Highlighted

@kmclorg

 

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

 

 

Regards,
Zubair


Highlighted

@kmclorg

 

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
    )
)
Regards,
Zubair


View solution in original post

Highlighted

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.

Highlighted
Super User III
Super User III

Hi @kmclorg,

 

Here's yet another way to solve the problem using Query Editor and PowerPivot.  Download the file from here.

 

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

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

View solution in original post

Highlighted

Hi @kmclorg,

 

You are welcome.  If my reply helped, please mark it as Answer.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

Hi what should I do if I want to use the DISTINCTCOUNT function on a column of the summarized table?

Highlighted
Anonymous
Not applicable

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors