cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
xl0911
Helper III
Helper III

Measure that counts Active subscribers

Hello,

 

This is a continuation ticket from a previous ticket I opened, following my prompting mistakes, which accumulated a lot of messages that were simply difficult to follow and I was advised to open a new card. (https://community.powerbi.com/t5/Desktop/Product-table-and-subscription-table/m-p/2679518#M938530)

 

My Issue:

 

Attached File

In advance I should mention that a downloadable file is attached, there is also a formula that needs a little more precision (the formula was written by tamerj1, many thanks to him for the help)

Attached File: https://we.tl/t-LtP4V0L6H0

 

schema:

2022-08-04_08h59_52.png

 

Desired results: 

2022-08-04_08h58_44.png

 

I need a measure that counts how many Active Status was in every month (or year depending on the filter context).

The rules are:

1. count only if in the end of the period the Subscription ID was in status "Active"

2. if there is in the same Status Date a status change, we need to take the highest "Status History ID" (PK) so this field will be the "Tiebreaker"

 3. it should count as "Running Count", so if a Subscription ID was Active at Jan-22 and there is no status change whill Feb, Mar... the measure still needs to count it on those months

 

Hope every thing is clear.

 

 

 

 

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

I have contributed to your original thread.  Have you reviewed my solution? 


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

Hi Ashish_Mathur,

 

Your solution is this DAX formula:

 DISTINCTCOUNT(Data[Product Id])

 

And it's not what I need as you can see in my question.

That is not correct.  Please see the transformations (in the Query Editor) that i have applied to your dataset.


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

Thank you but I need a DAX solution.

Hi @xl0911 ,

 

I am sorry for late reply. Could you tell me if your issue has been solved? If it is, kindly Accept the helpful reply as the solution. More people will benefit from it.

Or you are still confused about it, please share your sample file with me agin. It seems that your transfer expired. I do not have access to your sample file. 

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello,

 

I solved it by the SQL Server data source level.

 

lbendlin
Super User
Super User

You need to use a disconnected dates table for that, or a crossjoin.

unfortunately I don't know how to do it 😕 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.