Forum Discussion
Count number of Risks under 2 different categories in 2 different columns, 2 tables - Matrix visual
Hi Everyone,
I am new to Power BI and I am need of urgent help with below to create a Heat Map using a Matrix Visual.
I have 2 tables
Table 1 - Risk Summary Table having below columns
2 Impact Level Columns
- Inherent Impact Level (with values like Critical, High, Medium, Low against Risk IDs with Risk ID's being unique)
- Residual Impact Level (with values like Critical, High, Medium, Low against Risk IDs with Risk ID's being unique)
2 Risk Level Columns
- Inherent Risk Level (with values like Significant, High, Moderate, Minor against Risk IDs with Risk ID's being unique)
- Residual Risk Level (with values like Significant, High, Moderate, Minor against Risk IDs with Risk ID's being unique)
Table 2 - Risk Probability having below columns
- Inherent Probability Level (with values like Highly Possible, Likely Possible, Moderately Possible, Remotely Possible against Risk IDs with Risk ID's being unique)
- Residual Probability Level (with values like Highly Possible, Likely Possible, Moderately Possible, Remotely Possible against Risk IDs with Risk ID's being unique)
Table 1 & Table 2 are connected by relationship - Risk ID column
Table 1
| Risk ID | Inherent Impact Level | Residual Impact Level | Inherent Risk Level | Residual Risk Level |
| 50001 | Significant | High | Critical | Critical |
| 50002 | High | Significant | Critical | High |
| 50003 | Significant | Moderate | Low | Medium |
| 50004 | Minor | Moderate | High | Low |
Table 2
| Risk ID | Inherent Probability Level | Residual Probability Level |
| 50001 | Highly Possible | Likely Possible |
| 50002 | Highly Possible | Moderately Possible |
| 50003 | Likely Possible | Highly Possible |
| 50004 | Moderately Possible | Remotely Possible |
I need to create One Matrix visual (Heat Map purposes) to show the count of Risk IDs using Inherent Risk Level & Residual Risk Level as slicer/selection options (Currently I have 2 Matrix Visual and I need to show this in only One Matrix Visual)
I need to use Risk Level as Slicer and
when ever anyone selects Inherent Risk Level the Matrix should only show count of Risk IDs for relevant Inherent Matrix (Probability & Imapact)
when ever anyone selects Residual Risk Level the Matrix should only show count of Risk IDs for relevant Residual Matrix (Probability & Imapact)
| Probability Level | Minor | Moderate | High | Significant |
| Highly Possible | XX | XX | XX | XX |
| Likely Possible | XX | XX | XX | XX |
| Moderately Possible | XX | XX | XX | XX |
| Remotely Possible | XX | XX | XX | XX |
Please please help me
Thanks
Sampath Giri D N
You could use bookmarks which show & hide only 1 version of the table and then have a button which flips the bookmark
Hi SampathGiriDN ,
You can try these steps.
1 Unpivot the Table 2. Let it like this ( select IDs and unpivot other columns):
2 Configure the relationship between Table1 and Table2, Cross filter direction to both.
3 Create a new table via distinct(union(VALUES('Table 1'[Inherent Impact Level]),VALUES('Table 1'[Residual Impact Level]))):
4 Last, create a tag measure named if_1:
if_1 = SWITCH(TRUE(), ISFILTERED('Table 1'[Inherent Risk Level])&&SELECTEDVALUE('Table 2'[Attribute])="Inherent Probability Level",1, ISFILTERED('Table 1'[Residual Risk Level])&&SELECTEDVALUE('Table 2'[Attribute])="Residual Probability Level",1, not(OR(ISFILTERED('Table 1'[Residual Risk Level]),ISFILTERED('Table 1'[Inherent Risk Level])))&&SELECTEDVALUE('Table 2'[Attribute])="Residual Probability Level",1,0)Put this tag in matrix table's filter pane and set show items is 1
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
johnt75Super User
You could use bookmarks which show & hide only 1 version of the table and then have a button which flips the bookmark
- v-chenwuz-msft
Community Support
Hi SampathGiriDN ,
You can try these steps.
1 Unpivot the Table 2. Let it like this ( select IDs and unpivot other columns):
2 Configure the relationship between Table1 and Table2, Cross filter direction to both.
3 Create a new table via distinct(union(VALUES('Table 1'[Inherent Impact Level]),VALUES('Table 1'[Residual Impact Level]))):
4 Last, create a tag measure named if_1:
if_1 = SWITCH(TRUE(), ISFILTERED('Table 1'[Inherent Risk Level])&&SELECTEDVALUE('Table 2'[Attribute])="Inherent Probability Level",1, ISFILTERED('Table 1'[Residual Risk Level])&&SELECTEDVALUE('Table 2'[Attribute])="Residual Probability Level",1, not(OR(ISFILTERED('Table 1'[Residual Risk Level]),ISFILTERED('Table 1'[Inherent Risk Level])))&&SELECTEDVALUE('Table 2'[Attribute])="Residual Probability Level",1,0)Put this tag in matrix table's filter pane and set show items is 1
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-chenwuz-msft Thank you so much tried this and it worked, our internal team providing me with the source reports has now changed the data sources and formats. I am hoping I can still apply this solution there. will keep you posted or hoping to connect back for any help, thank you for all the support.
