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
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
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.