Creating risk matrix on PowerBI

[em101]

Hi there, 

I'm trying to use a data feed of Jira risks (which can be converted into Excel) to then upload into PowerBI to create a risk matrix (project management tool like below). Does anyone know how I can transform the data in PowerBI (without first going into Excel) to allocate number values to the risk likelihood and consequence to create a risk matrix in PowerBI? At the moment the risk likelihood and consequence are just words rather than number values. The risk matrix grid will also need number values assigned to it. So far I have only come across videos where people have transformed the data in Microsoft Excel first before then loading on to PowerBI. Thank you. 

[giammariam · Accepted solution]

@em101, I apologize for the delay on this. I have attached a .pbix for a possible way to implement the risk matrix. This file contains power query transformations, relationships, measures, and the matrix visual itself.

You'll definitely need to adjust things but hopefully this is enough to get you going. If so, please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.

[LuckyD]

Hello @giammariam 

I need to show the number of Issues for each Likelihood and consequence. How do I modify the PBIX file you attached? Please advise! Thanks in advance!

[giammariam · Accepted solution]

Hi @em101. See attached. 

Here's a relevant link that may be helpful in the future for you related to sorting: Sort one column by another column in Power BI

If this is enough to get you going please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.

[giammariam]

Hi @em101. Try updating the measure to the following:

_issueKeyConCat = 
var newLine =
"
"
var issueKey = SELECTEDVALUE(Jira[Issue Key], CONCATENATEX(Jira, Jira[Issue Key], newLine))
var result = SWITCH(
    true,
    ISBLANK(issueKey), "",
    issueKey
)
RETURN result

 
Also, you will need to adjust the matrix dimensions and probably the font size for the matrix values to get everything to fit.

If this is enough to get you going please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.

[em101]

@giammariam thanks so much, that seemed to largely work. I'm just sending you a direct message as I have almost got it to work just one more small query. Thanks 

[giammariam]

Hi @em101, apologies for the delay. In the first Merged Queries step I'm joining on [Residual Likelihood] (Jira query) to [Likelihood] (Likelihood query). Then in the subsequent Expanded Likelihood step, I'm bringing the [Likelihood Number] field into the Jira query.

The same pattern exists for bringing in the Consequence Number.

If this is enough to get you going please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.

[em101]

Hi @giammariam 

Thanks so much for clarifying the Merge and Expand steps. When I entered in the same IssueKeyConCat DAX formula to the one you used (I changed it to refer to Jira2 Query data not your original Jira Query), I seem to get the below error message on the Risk Matrix visual. I'm trying to work with about 60 rows of risks this time. Do you know what I might be doing wrong with the DAX? Thank you. 

[giammariam · Accepted solution]

@em101, I apologize for the delay on this. I have attached a .pbix for a possible way to implement the risk matrix. This file contains power query transformations, relationships, measures, and the matrix visual itself.

You'll definitely need to adjust things but hopefully this is enough to get you going. If so, please consider liking this reply and choosing it as the solution. Otherwise, I'm happy to help further.

[em101]

Hi @giammariam, sorry to bother you again. In the Power BI file, I was just wondering in the 'Jira' table did you have to manually enter in the 'Likelihood Number' and 'Consequence Number' for each risk or were you able to set this up with some sort of formula/DAX? If you are able to provide any sort of summary of the steps you applied (particularly around doing the comp_key_Likelihood_Consequence and _issueKeyConCat) that would be great for my understanding. Thank you 

[giammariam]

Hi @em101, sure. The 'Liklihood Number' and 'Consequence Number' were not manually added to the Jira query. Below are the steps that I implemented. The heavy lifting was done in Power Query. #4 is the critical piece.

1. I created 3 lookup queries using the "Enter Data" option: "Consequence", "Likelihood", "5x5 Unpivoted." My understanding is that these are static.

2. The "Jira" also used the "Enter Data" option, but in your implementation you'll want to use your source (CSV, Excel, etc.)

3. The [comp_key_Likelihood_Consequence] is from an abandoned implementation that I had tried earlier. My apologies for not removing it and creating confusion. You can disregard it. 

4. In the Jira query, the Likelihood query is first merged into the Jira query on fields [Residual Likelihood] -> [Likelihood] and the [Likelihood Number] from the Likelihood query is added.  Then Consequence query is merged into the Jira query on fields [Residual Consequence] -> [Consequence] and the [Consequence Number] from the Consequence query is added.

By adding the two fields in step 4 to the Jira query, we are able to create the following model in Power BI Desktop, and map from the Jira query to the 5x5 Unpivoted query through the Likelihood and Consequence lookup queries:

I hope this helps. 

[em101]

Hi @giammariam 

Thanks so much for your response. 

For Step 4, I can see the "Merge Queries" option which I understand you use for merging fields [Residual Likelihood] -> [Likelihood], but what do you use to add the Likelihood Number query to the Jira query? Is it on the Append Queries dropdown or do you use something else? 

Also can you please explain the steps on how you got the background and foreground colour measures to appear on the Matrix visual? 

Many thanks for your help. 

[em101]

Hi @giammariam

Thank you so much for your help with this - it is appreciated 🙂 

[giammariam]

Hi @em101, I'm not completely familiar with concept of jira risks. Do you mind providing a an example dataset of what it looks before it gets transformed in Excel (here's how)? It may be helpful to provide another example of what that same dataset would like after the transformation. 

If I had those, I'd be happy to give this a shot.