Compounds in beer & snacks problem

[jgrob3]

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, the flavor ID it's linked to, and the flavor name (merged into the table)

tblBeer: list of beers and the flavors each contains

tblSnacks: list of snacks and the flavors each contains

What I'm trying to produce is a table showing the number of compounds that are common to each beer and each snack combination:

This is nearly giving me the result I want except that it's only showing the number of compounds that are common to the flavors that are common to both the beer and the snack.  Eg if both have a flavor of "SALTY" but the beer also has "SOUR" (with its list of compounds including "Glutamic acid") and the snack has "CORN" (with its list of compounds that also includes "Glutamic acid"), what I'm getting is the list of just the compounds in the SALTY flavor.  "Glutamic acid" is missing.

If I click on the row or column totals, I see the correct compounds displayed, including "Glutamic acid".

My thinking was to create the correct filter context by creating a list of all the beer flavors (without the snacks filter applied), then joining this via UNION with a list of all the snack flavors (without the beer filter applied) ... but I can't quite seem to work out how to do this.

I thought I could get it using the DAX below;

Number of Compounds =
 CALCULATE (
     DISTINCTCOUNT ( compounds[id] ),
     compounds[name.1]
         IN UNION (
             CALCULATETABLE (
                 ALL ( tblSnacks[Flavor] ),
                 ALLSELECTED ( tblSnacks[Item] ),
                 ALL ( tblBeer[Item] )
             ),
             CALCULATETABLE (
                 ALL ( tblBeer[Flavor] ),
                 ALLSELECTED ( tblBeer[Item] ),
                 ALL ( tblSnacks[Item] )
             )
         )
 )

[name.1] is the flavor name

[Item] is the name of the snack or beer

The rows of the table above contain tblBeer[Item] and the columns contain tblSnacks[Item].

Unfortunately, this doesn't quite work!

Can anyone point me in the right direction please?

Thanks

Jeff 

[v-frfei-msft]

Hi @jgrob3,

Kindly share your sample data and excepted result to me.

Regards,

Frank

[jgrob3]

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

 

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 · Accepted solution]

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: