Forum Discussion

jgrob3's avatar
jgrob3
Advocate I
7 years ago
Solved

Compounds in beer & snacks problem

I have 4 tables in my model:   flavors: list of flavors & IDs   compounds: list of the component compounds that make up each flavor. Each row in the component table has its individual compound ID...
Need Help
  • jgrob3's avatar
    jgrob3
    7 years ago

    Thank you Frank for all your help & efforts!

     

    There might be a better way of doing this but I ended up solving my own problem using the following solution:

    • Duplicate the compounds_flavors table to create a snacks compounds_flavors table and a separate beer compounds_flavors table
    • Merge the compound name and description into each table (not really necessary but it made it easier than just having an ID)
    • The tblSnacks is joined to snacks compounds_flavors using the [flavor] field with a many-to-many join and "Both" cross-filtering enabled.  The tblBeer is joined to its corresponding table the same way.
    • My measure to count the number of compounds in common then became quite simple:
    Number of Compounds =
COUNTROWS (
    DISTINCT (
        INTERSECT (
            VALUES ( 'beer compounds_flavors'[compound_id] ),
            VALUES ( 'snacks compounds_flavors'[compound_id] )
        )
    )
)

    Because I'm matching on the flavor names (the list of ingredients for each beer & snack didn't have flavor ID), I also created a couple of extra tables to show which flavors matched (merge query > inner join) and which didn't (merge query > left anti join).

     

    The end result:

     

     

jgrob3's avatar
jgrob3
Advocate I
7 years ago

Hi Frank

 

The starting point is one table of food, that I split into two (using a filter based on Type): one for beer and one for snacks.  After I unpivot, I end up with:

 

tblBeer

 tblBeer

tblSnacks

 

My table of flavors looks like this:

 

... and the Compounds table is

 

I've connected the tables together:

 

There are bi-directional relationships between:

tblSnacks[Flavor] > flavors[name]

tblBeer[Flavor] > flavors[name]

 

flavors[id] is joined with compounds[flavor_id]

 

As mentioned above, I'd like to count the number of distinct compounds that are common to each combination of Beer and Snack.  To do this, what I'm trying to do is to use the flavors that are in each beer or snack to extract a list of the compounds that are in each flavor.

 

At the moment however, my measure is only giving me a list of the compounds in flavors that are common to each beer/snack combination.  But what I'd like to get is a list of the common compounds, regardless of whether the flavor is also in common (see salty/sour example above).

 

I'm obviously missing something!

 

Thanks

jgrob3's avatar
jgrob3
Advocate I
7 years ago

Thank you Frank for all your help & efforts!

 

There might be a better way of doing this but I ended up solving my own problem using the following solution:

  • Duplicate the compounds_flavors table to create a snacks compounds_flavors table and a separate beer compounds_flavors table
  • Merge the compound name and description into each table (not really necessary but it made it easier than just having an ID)
  • The tblSnacks is joined to snacks compounds_flavors using the [flavor] field with a many-to-many join and "Both" cross-filtering enabled.  The tblBeer is joined to its corresponding table the same way.
  • My measure to count the number of compounds in common then became quite simple:
Number of Compounds =
COUNTROWS (
    DISTINCT (
        INTERSECT (
            VALUES ( 'beer compounds_flavors'[compound_id] ),
            VALUES ( 'snacks compounds_flavors'[compound_id] )
        )
    )
)

Because I'm matching on the flavor names (the list of ingredients for each beer & snack didn't have flavor ID), I also created a couple of extra tables to show which flavors matched (merge query > inner join) and which didn't (merge query > left anti join).

 

The end result: