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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors