Forum Discussion
State-transition table
Hello,
I want to create a state-transition table using Power BI. My goal is something like the following table
| From 2014/To 2015 | A | B | C | |
| A | 3 | 1 | 1 | 5 |
| B | 2 | 3 | 1 | 6 |
| C | 0 | 2 | 3 | 5 |
| 5 | 6 | 5 | 16 |
The table describes the following: From 2014 to 2015 3 persons stayed in category A, 1 person moved from A to B, 1 person moved from A to C, 2 persons moved from B to A, 3 persons stay in B and so on.
I want the years to be dynamic, for example, I would also like to see the transitions from 2010 to 2015 or 2012 to 2014.
How does my data have to look like and how do I create such a state-transition table? I didn't find any information about that and I don't know how to start. I am quite new to PowerBI.
Best regards
Greg_Deckler I solved the problem. I used a table like the following:
Start Year Category Start End Year Category End Amount 2013 A 2014 A 2 2013 B 2014 A 3 2014 A 2015 B 4 Then I used category start as rows, category end as column and amount as values and two filters for start year and end year, respectively.
Best regards
3 Replies
Anonymous - Taking a closer look at this. However, right off the bat, I do not see how you accomplish this without using a disconnected table for either your row or column states. This assumes that what you want is this presented in a matrix visualization.
I'm fairly certain that you could get there with your data in this kind of state:
Year State ID
2014 A 1 2014 B 2 2014 C 3 2014 A 4 2014 A 5 2014 B 6 2015 A 1 2015 B 2 2015 C 3 2015 A 4 2015 A 5 2015 C 6 Might be able to do it as well with
ID 2014 2015
1 A A 2 B B 3 C C 4 A B But doubt that would be better.
Greg_Deckler I solved the problem. I used a table like the following:
Start Year Category Start End Year Category End Amount 2013 A 2014 A 2 2013 B 2014 A 3 2014 A 2015 B 4 Then I used category start as rows, category end as column and amount as values and two filters for start year and end year, respectively.
Best regards
Thank you for your answer. Yes, at the moment I have that first type of table, but I don't know how to move on 😕 If you have any idea Greg_Deckler, please let me know.
