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.
Hi everyone,
I would like to analyse a data set by identifying relationships through collaborations between companies across different projects.
In one column I have the project name and in another each participant of each project
Column 1:Project Column 2: Participants
Project A Company 1
Project A Company 2
Project A Company 3
Project B Company 3
Project B Company 4
Project C Company 1
The question I try to analyse is how often e.g. Company A has worked with Company B across all projects in the dataset.
Do you have any tips on how to either organise the data set or use Power BI for this analysis and visualisation? Any direction to former posts in this forum is highly appreciated.
Bests,
Nick
Solved! Go to Solution.
No problem.
Here is a very simple PBIX which I hope helps with setting up this sort of data model.
The steps I followed were:
Projects with Both Companies = CALCULATE ( DISTINCTCOUNT ( ProjectParticipation[Project] ), CALCULATETABLE ( SUMMARIZE ( ProjectParticipation, ProjectParticipation[Project] ), ALL ( Company ), USERELATIONSHIP ( ProjectParticipation[Company], 'Filter Company'[Filter Company] ) ) )This uses the pattern from DAX Patterns.
Hopefully that's useful. Please post back if needed 🙂
Regards,
Owen
Hi @Anonymous
Take a look at Basket Analysis on DAX Patterns.
https://www.daxpatterns.com/basket-analysis/
Also search for "basket analysis" on the forum as I think I have seen it a few times including here.
In terms of the Basket Analysis pattern on DAX Patterns, that pattern counts distinct Orders which contain Products matching the selection on two separate slicers.
In your case, you would use Projects in the place of Orders and Companies in the place of Products.
Regards,
Owen
@OwenAuger Thank you for that.
I feel that the steps provided in the link on daxpatterns Basket analysis is already a jumpstart for me. However, being not familiar enough, I would hope for a bit more beginner-style guidance on how to generate the filter tables first.
Adopting my data to the example from the link you shared, the displayed report could not create the tables due to some error in the data tables.
Any additional help - thanks in advance!
Bests,
Nick
No problem.
Here is a very simple PBIX which I hope helps with setting up this sort of data model.
The steps I followed were:
Projects with Both Companies = CALCULATE ( DISTINCTCOUNT ( ProjectParticipation[Project] ), CALCULATETABLE ( SUMMARIZE ( ProjectParticipation, ProjectParticipation[Project] ), ALL ( Company ), USERELATIONSHIP ( ProjectParticipation[Company], 'Filter Company'[Filter Company] ) ) )This uses the pattern from DAX Patterns.
Hopefully that's useful. Please post back if needed 🙂
Regards,
Owen
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 |
---|---|
112 | |
97 | |
85 | |
68 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |