Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a list of projects and for each project there can be more than one risk. My goal is to combine the risks by project rather than combine all of the risks into a single entry. Is there a basic approach I can take to the DAX formula? Can I do this with a measure called "Combined Risks". My goal is to have one row per project in the final report.
Mike
Solved! Go to Solution.
Hey @mstone ,
Can you try to copy and paste one of the following expressions?
Combined column = CALCULATE(CONCATENATEX(DIM_PROJECT_RISK, [RiskTitle], UNICHAR(10)), ALLEXCEPT(DIM_PROJECT_RISK, DIM_PROJECT_RISK[ProjectId]))
AsMeasure = CALCULATE(CONCATENATEX(VALUES(DIM_PROJECT_RISK[RiskTitle]), [RiskTitle], UNICHAR(10)), ALLEXCEPT(DIM_PROJECT_RISK, DIM_PROJECT_RISK[ProjectId]))
Here is a link you can download to a sample: https://1drv.ms/u/s!An8CCFsOzw0uhQYExSCu5Hj9NDon?e=7B3enE
@mstone
Can you paste here a screenshot of dummy data that represent your data table?
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Sorry for the delay. Here is an example of what it looks like now and how I would like it to look:
ProjectID | Risks |
PRJ6319 | Risk 1 |
PRJ6320 | Risk 1 |
PRJ6320 | Risk 2 |
PRJ6320 | Risk 3 |
PRJ6321 | Risk1 |
PRJ6321 | Risk 2 |
ProjectID | Risks |
|
PRJ6319 | Risk 1 |
|
PRJ6320 | Risk 1 Risk 2 Risk 3 |
|
PRJ6321 | Risk 1 Risk 2 |
|
Hi,
Try this measure
All risks = CONCATENATEX(VALUES(Data[Risks]),Data[Risks],",")
Drag Project ID and the All Risks measure to the Table visual.
Hope this helps.
hi @mstone ,
You can use the CONCATENATEX function. Please see the measure below with expected output:
Risks := CONCATENATEX(Risks, Risks[Risks], UNICHAR(10))
Thank you, hnguy71! Will that parse them by projectID? The orginal data is not in sequence. It consists of different risk statements. I'll give it a try.
Using that syntax, I ended up with all entries from all projects combined in one list rather than parsed by project. Is there something I can do to modify the formula?
Combined column = CONCATENATEX(DIM_PROJECT_RISK,DIM_PROJECT_RISK[RiskTitle],unichar(10))
Hi @mstone ,
that's interesting, it should group them out on it's own. Try this instead:
Risks := CALCULATE(CONCATENATEX(Risks, Risks[Risks], UNICHAR(10)), ALLEXCEPT(Risks, Risks[ProjectID]))
Btw, ProjectID and Risks both needs to be in the same visual for this to work.
Just tried that and received the following error message:
They are in the same visual. Perhaps I have made a sytax error. I may not see your reply until tomorrow morning. I do appreciate your help and patience!
Hey @mstone ,
Can you try to copy and paste one of the following expressions?
Combined column = CALCULATE(CONCATENATEX(DIM_PROJECT_RISK, [RiskTitle], UNICHAR(10)), ALLEXCEPT(DIM_PROJECT_RISK, DIM_PROJECT_RISK[ProjectId]))
AsMeasure = CALCULATE(CONCATENATEX(VALUES(DIM_PROJECT_RISK[RiskTitle]), [RiskTitle], UNICHAR(10)), ALLEXCEPT(DIM_PROJECT_RISK, DIM_PROJECT_RISK[ProjectId]))
Here is a link you can download to a sample: https://1drv.ms/u/s!An8CCFsOzw0uhQYExSCu5Hj9NDon?e=7B3enE
Confirmed that the second formula is working perfectly. Thank you so much!!
I tried both. The first seemed to produce duplicate entries but the second appeared to work perfectly. Due to a save issue with the file I had to set up the simple table again and it seems to be functioning differently than before. Just troubleshooting the difference but I think the second formula may have been the solution. 🙂 If I can verify that, I will definitely click "Accept as a Solution". Thank you for your help!
The tables didn't paste as desired but the idea is to combine the risks in a single cell so the result is a single project number entry. Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |