Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Basket Analysis with Power BI

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

Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    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:

    1. Create the table ProjectParticipation (usine Enter Data in this case) which is the table you originally posted.
    2. Created a Company dimension table by referencing ProjectParticipation and grouping by the Company column. (You could equally create this from an independent list of Companies.)
    3. Created a 'Filter Company' table by referencing Company, and renaming the Company column to Filter Company.
    4. Created relationships as follows:

       

    5. Created the measure Projects with Both Companies:
      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.
    6. Created a basic report page with slicers on Company[Company] and 'Filter Company'[Filter Company]:

       

    7. The above shows that Company 1 & Company 3 have one project in common. You could combine this measure with others to show, for example, what fraction of Company A's projects were shared with Company B (some examples on DAX Patterns).

     

    Hopefully that's useful. Please post back if needed :)

     

    Regards,

    Owen

3 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    7 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

      • OwenAuger's avatar
        OwenAuger
        Icon for Super User rankSuper User
        7 years ago

        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:

        1. Create the table ProjectParticipation (usine Enter Data in this case) which is the table you originally posted.
        2. Created a Company dimension table by referencing ProjectParticipation and grouping by the Company column. (You could equally create this from an independent list of Companies.)
        3. Created a 'Filter Company' table by referencing Company, and renaming the Company column to Filter Company.
        4. Created relationships as follows:

           

        5. Created the measure Projects with Both Companies:
          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.
        6. Created a basic report page with slicers on Company[Company] and 'Filter Company'[Filter Company]:

           

        7. The above shows that Company 1 & Company 3 have one project in common. You could combine this measure with others to show, for example, what fraction of Company A's projects were shared with Company B (some examples on DAX Patterns).

         

        Hopefully that's useful. Please post back if needed :)

         

        Regards,

        Owen