How do I change DAX script to count things differently?

[pmcinnis]

The table below titled 'Desired Output' shows the output I want. It's produced from the data in tables 'Results' and 'Guidelines'. 'Results' is a table of chemical concentration results. The column 'result type' is the type of sample from which the concentrations are measured. 'Guidelines' is a table of health guidelines for the various chemicals. Concentrations should be below the guidelines for public safety. 'Desired Output' counts the number of results (for each result type for each chemical) that are equal to or over the guideline and equal to or over 1/2 the guideline.

The DAX script I'm using doesn't quite give me the above Desired Output table. Instead it gives me the Output table below. It counts results for chemicals f and g even though chemicals f and g don't have a guideline to compare to.

Below is the DAX script I'm using. I use it to create six measures which I use to create the above table visuals. I modify the script as needed to create separate measures for result types R, T and D, as well as for comparing results to the guideline and 1/2 the guideline:

My question is: How do I modify the above DAX script to not count results for chemicals that don't have guidelines such as chemicals f and g?

Thanks in advance

[v-chuncz-msft · Accepted solution]

@pmcinnis 

You may add IF as follows.

IF (
    NOT (
        ISBLANK (
            CALCULATE (
                SELECTEDVALUE ( GUIDELINES[guideline] ),
                CROSSFILTER ( RESULTS[name], GUIDELINES[name], BOTH )
            )
        )
    ),