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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.