Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
em101
Frequent Visitor

Creating risk matrix on PowerBI

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. 

em101_0-1699483660212.png

 

 

2 ACCEPTED SOLUTIONS
giammariam
Super User
Super User

@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.

giammariam_0-1700529866440.png

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.





Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

View solution in original post

giammariam
Super User
Super User

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.





Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

View solution in original post

12 REPLIES 12
LuckyD
New Member

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
Super User
Super User

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.





Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!
giammariam
Super User
Super User

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.



Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

@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
Super User
Super User

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.

giammariam_0-1701963323053.png


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.





Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

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. 

 

em101_1-1702000960230.png

 

em101_0-1702000884311.png

 

giammariam
Super User
Super User

@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.

giammariam_0-1700529866440.png

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.





Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

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 

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.

giammariam_1-1701099573064.png

 

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:

giammariam_0-1701099259722.png

 

I hope this helps. 



Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

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? 

em101_0-1701315775044.png

 

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. 

 

Hi @giammariam

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

giammariam
Super User
Super User

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. 



Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors