Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Desired results:
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.
Hi,
I have contributed to your original thread. Have you reviewed my solution?
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.
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.
You need to use a disconnected dates table for that, or a crossjoin.
unfortunately I don't know how to do it 😕
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |