cancel
Showing results for
Did you mean:
Highlighted
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

 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

3 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User I

Hi @kmclorg

See the attached file here

With your sample data and solution

Regards,
Zubair

Highlighted
Super User I

@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

Highlighted
Helper I

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

9 REPLIES 9
Highlighted
Super User I

Hi @kmclorg

See the attached file here

With your sample data and solution

Regards,
Zubair

Highlighted
Super User I

@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
Super User I

@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

Highlighted
Helper I

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

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
Highlighted
Helper I

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

Highlighted
Super User III

Hi @kmclorg,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Frequent Visitor

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,

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

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors