Forum Discussion

pmcinnis's avatar
pmcinnis
Helper III
6 years ago

Need DAX script to make table

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.

 

 

 

4 Replies

  • FrankAT's avatar
    FrankAT
    Community Champion

    Hi pmcinnis,

    following solution, see figue.

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

     


    Regards FrankAT

    • pmcinnis's avatar
      pmcinnis
      Helper III

      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's avatar
        FrankAT
        Community Champion

        Hi pmcinnis,

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

         

        Parts of the solution

         

         Regards FrankAT