Need DAX script to make table

[pmcinnis]

I need to produce the table 'Desired Output' from the data in the tables 'Results' and 'Guidelines'.

• 'Results' is a table of chemical concentration results.
  • 'Factory' is the factory where the chemical was sampled.
  • 'result type' is the type of sample.
• 'Guidelines' is a table of health guidelines for the various chemicals. Chemical concentration results should be below the guidelines to ensure public safety.
• The table 'Desired Output' presents the number of results above guideline for each chemical at each factory, for result types K and L but not result type J.
• Many chemicals don't have guidelines, so those chemicals are left out of the table 'Desired Output'
• Where the number of results above guideline for a given chemical at a given factory (for result types K and L) is zero,  the entry in 'Desired Output' is a blank rather than zero
• Where a chemical has no results above guideline at any factory (for result types K and L), that chemical is left out of the table 'Desired Output'. In the example below, chemicals c and d are left out of 'Desired Output' for this reason.

[FrankAT]

Hi @pmcinnis,

following solution, see figue.

For the other two measure change only the marked parts to 'b' and 'e'.

Regards FrankAT

[pmcinnis]

Hi Frank, thanks for the response and the effort you put into it. It was my oversight to not mention that there are 450 chemicals and I'm unable to do them one-by-one. I was hoping get a solution that does all chemicals at once.

[FrankAT]

Hi @pmcinnis,

here is the solution you need, I hope ;-):

Parts of the solution

 Regards FrankAT

[pmcinnis]

Thanks Frank, much appreciated. Many of the chemicals don't have guidelines and as a result the guidelines table doesn't have rows for those chemicals i.e. they don't appear in the guidelines table at all.  There are 450 chemicals and the guidelines table has only 130 guidelines. Will this script still work? Thanks again.