Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kmclorg
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
Zubair_Muhammad
Community Champion
Community Champion

Hi @kmclorg

 

See the attached file here

With your sample data and solution

 

007.png

 


Regards
Zubair

Please try my custom visuals

View solution in original post

@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

Please try my custom visuals

View solution in original post

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
Ashish_Mathur
Super User
Super User

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/

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Zubair_Muhammad
Community Champion
Community Champion

Hi @kmclorg

 

See the attached file here

With your sample data and solution

 

007.png

 


Regards
Zubair

Please try my custom visuals

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.

@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

Please try my custom visuals

@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

Please try my custom visuals
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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.