Compare Initial Status to Current Status

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.

✨ Follow us on LinkedIn and  to our YouTube channel

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.