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

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