Forum Discussion

pmcinnis's avatar
pmcinnis
Helper III
6 years ago
Solved

How do I change DAX script to count things differently?

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

 

 

 

 

 

 

 

 

 

  • pmcinnis 

     

    You may add IF as follows.

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

     

1 Reply

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support

    pmcinnis 

     

    You may add IF as follows.

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