Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello, new user, hope I am posting this in the correct spot. my apologies if not.
I have a column with initial status of customers at the start of a new program.
I created a measure to count the number of instances of each possible status. Such as: Converted, Eligible, Stopped.
This works as expected, I have the status types and the number of each. (Please, ignore the actual status, there are a few to clean up)
I did the same thing for Current Status, and that worked too.
Now, I need to compute the difference in number at the initial start of the program to what the current number is.
So, for example, looking to get -188 as the difference in the number of Converted, and so on.
Was hoping that a new measure subtracting the measure for Initial from the Current would do the trick.
But, it does not.
Any possible solutions would be much appreciated.
Thanks.
Solved! Go to Solution.
@Anonymous ah! I see the issue, so you have two status columns in your table one for initial and one for final. This is what you need to do to make it work. This is all about modeling and a bit of DAX.
- Create another table called Status and this table will contain a unique list of Status values from both the Initial and Final status column
- Set the relationship of this table with your data table on the status column. There will be two relationship, one will be status table status column with initial status column (it will be active relationship) and it will be one to many
- Create another relationship between the status column from the status table and the final status column, it will be an inactive relationship and it will be one to many.
Once all the above is done, add following DAX measures
Initial Status Count = COUNTROWS ( YourDataTable )
Final Status Count =
CALCULATE (
[Initial Status Count],
USERELATIONSHIP ( StatusTable[Status Column], YourDataTable[Final Status Column] )
)
Delta Initial and Final Status Count = [Initial Status Count] - [Final Status Count]
To visualize, use Status column from the new Status table and the above measures and you will have the solution.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous seems like the solution I provided is working. You should accept the actual reply as a solution so that others can easily find it.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Anonymous all the best. Keep posted.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
OK, I think I understand. I will give it a go. Thanks again!
@Anonymous ah! I see the issue, so you have two status columns in your table one for initial and one for final. This is what you need to do to make it work. This is all about modeling and a bit of DAX.
- Create another table called Status and this table will contain a unique list of Status values from both the Initial and Final status column
- Set the relationship of this table with your data table on the status column. There will be two relationship, one will be status table status column with initial status column (it will be active relationship) and it will be one to many
- Create another relationship between the status column from the status table and the final status column, it will be an inactive relationship and it will be one to many.
Once all the above is done, add following DAX measures
Initial Status Count = COUNTROWS ( YourDataTable )
Final Status Count =
CALCULATE (
[Initial Status Count],
USERELATIONSHIP ( StatusTable[Status Column], YourDataTable[Final Status Column] )
)
Delta Initial and Final Status Count = [Initial Status Count] - [Final Status Count]
To visualize, use Status column from the new Status table and the above measures and you will have the solution.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi, thanks for the response. No, no errors. Just shows zeros for each category for the variance measure.
@Anonymous it should work, are you getting any error or what?
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.