Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
expected result
Hello,
I have a product table that looks like that:
Product Id | Product Name |
1 | Product Y |
2 | Product X |
3 | Product Z |
And I have a Subscription Product Status table that a product can be in Status "Active" or "Not Active".
Table Id (Unique) | Product Id | Status | Status Date |
1 | 1 | Active | 20/01/2018 |
2 | 1 | Not Active | 23/01/2018 |
3 | 2 | Active | 28/01/2018 |
4 | 3 | Active | 14/05/2018 |
5 | 1 | Active | 06/03/2019 |
6 | 1 | Not Active | 12/08/2019 |
7 | 3 | Not Active | 24/02/2020 |
8 | 1 | Active | 06/06/2020 |
9 | 2 | Not Active | 22/12/2021 |
10 | 3 | Active | 02/03/2022 |
As you can see this Subscription Status Table holds the historical status of every product, and every row represents a status change.
My goal is eventually to create a visual that shows Every month the number of Active products that I had.
I need a formula to create a measure to achieve this goal.
Thank you
Solved! Go to Solution.
I have updated the solution as requested to only check if the product was still active at the end of the month. (Originally you implied you wanted active anytime of the month)
Click here to download a solution
Now please click thumbs up and accept as solution.
It is not fair to keep changing to problem description.
If we the fix the problem then please accept the solution and raise a new ticket if you want to change the problem description.
Hi @xl0911
I don't understand what is the problem. Here is your sample file with the very same code
@xl0911
If there is a record with same ID and same Date then there should be a value from a third column that is different. Otherwise it is a dupicated record. But in this case what do you mean by largest ID while we're talking about the same ID same Date?
Hi tamerj1,
As you can see in the screenshot because we have 2 status's with the same date the measure counts it, and of course we need to count the second/largest Id (In the Status History Id field - number 9).
I attached link to download this file: https://we.tl/t-jZGBbCS8hW
Hope that makes sense, thank you !
Hi @xl0911
Sorry I was so busy today I didn't have the chance to look at it. Is it possible for the subsription ID to be repeated again and again? This is a big complication of the issue
In the "StatusHistory" Table the subscription ID can repeat again and again but in the Subscription table is unique (PK).
@xl0911
I would also suggest that in case you still have problems, is better to open a new ticket as this one became conjusted with too many replies and already difficult to track the thread order.
@xl0911
This just due to a missing "=" sign after the ">" sign in line # 14.
Here is the updated file https://www.dropbox.com/t/168x2xtLG8b4oHYc
Hi tamerj1, Any new to my last post ?
Thank you tamerj1, but still it's not working properly, as you can see in the attached screenshot, if in the same date the highest ID is Active, it should be count it (and it's not)
@xl0911
The subscription cannot start with Inactive status! Do you have the same scenario in your real data?
You are right subscription cannot start with Inactive status (also in my real data).
@xl0911
Ok but it is worth it that you check carefully with your real data and make sure no other issues.
Yes of course a unique column required. I'm outside office now. I will check once I return
I mean, same Subscription ID (is a foreign key) and same Status Date, so the "tie breaker" is the Staus History ID witch is a primary key on that Status History table.
Found my issue, a mean relationship with my date table.
Thank you !!!!!
I entered new value's so you can see the mistake, here is the link :https://we.tl/t-1hmf4eHwnk
Check Jan-2018 it suppose to return total 4 but it returns total 2.
Hi,
You may download my revised PBI file from here. The answer for Jan 2022 is 4.
Hope this helps.
Sorry @tamerj1, I think there is a bug in your DAX
Only one product was active in Feb 2018 but your report shows two.
Thanks for trying to help xl0911
Please can you double check my solution (see above).
Two pairs of eyes are better than one.
Ha Ha I see that tamerj1 has also posted solution.
Execllent !
It will be interesting to compare the output from both methods and see if they produce the same results.
Thank you xl0911 for a very clear explantion of your problem with example data as tables (not screen shots).
So I could import the data and build this solution. Wish all members gave such clear decsriptions!
Click here to download the solution
Quote @speedramps when you raise new problems if you can describe them all as nicely as this and I will receive an notification.
I created 3 measures
Thank you speedramps
I think your solution is not what I meant because Product Id 1 is not active in the end of the month so under product Id 1 in jan-2018 the count should be 0.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |