Need DAX scripts that provide new table and new measures for a table visual

[pmcinnis]

I'm hoping to get the DAX scripts that would build the table below in Power BI called 'Desired Output'. 'Desired Output' is produced from the data in the tables 'Results' and 'Guidelines'. I'm new to Power BI and DAX and seeing the scripts would help a lot in learning how the DAX functions work. If possible I'd like to get two solutions: one for a new table and one for a table visual. So the table visual scripts would be for new measures.

'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.

Thanks in advance

[ChrisMendoza · Accepted solution]

@pmcinnis -

You should be able to accomplish this

in a Table Matrix Visual setup like

using these two measures:

# of results at or above 1/2 the guideline =
CALCULATE (
    COUNTROWS ( RESULTS ),
    FILTER (
        RESULTS,
        RESULTS[result]
            >= DIVIDE (
                RELATED ( GUIDELINES[guideline] ),
                2,
                -1
            )
    )
)

# of results at or above guideline =
CALCULATE (
    COUNTROWS ( RESULTS ),
    FILTER (
        RESULTS,
        RESULTS[result]
            >= RELATED ( GUIDELINES[guideline] )
    )
)

[pmcinnis]

Thanks Chris. This solution doesn't seem to provide separate counts for R, T and D result types. How do I do that?

[ChrisMendoza]

@pmcinnis -

I don't understand what you mean.

Are you needing a Measure for some other type of Visual like a Card Visual? If I'm thinking correctly, that would mean you would have 6 Measures essentially all showing the same type of data just filtered differently.

[pmcinnis]

The DAX script doesn't seem to give separate counts for R, T and D result types. For example,  I think I would need three separate measures to get all three counts for results at or above guidelines. I think this works for the result type R count, is it correct?:

# of results at or above guideline for result type R =

CALCULATE(COUNTROWS(RESULTS),

FILTER(RESULTS,RESULTS[result]>=RELATED(GUIDELINES[guideline])),

FILTER(RESULTS,RESULTS[result type]="R"))

[ChrisMendoza]

@pmcinnis -

Oh I see now. Yes I am letting the Visual handle the filtering for me. Your modification should work I would think. I don't have the sample data anymore and would have to recreate.

Yeah, your's works. I used:

# of R results at or above guideline = 
CALCULATE (
    COUNTROWS ( RESULTS ),
    RESULTS[result type] = "R",
    FILTER (
        RESULTS,
        RESULTS[result]
            >= RELATED ( guidelines[guideline] )
    )
)

I am unsure if one is better than the other, maybe a little easier to read?

[pmcinnis]

Apologies Chris, I hadn't notice the Visuals filtering part of your original solution (am still quite new to this). Your solution is more efficient than mine which creates 6 different measures.

Wondering if you could help me with some refinements to this solution. In my actual RESULTS table there are chemicals that don't have a corresponding guideline in the GUIDELINES table; however the solution is counting all the rows of results for those chemicals rather than returning a blank (or zero) result. For example, for chemical f with 7 rows of data but no guideline it will return a count of 7 rather than a blank (I need it to return a blank).  So I'm wondering:

(i) How do I tweak the solution DAX script so that it returns a blank for chemicals that don't have guidelines?

(ii) What is the DAX script to create a Yes/No measure (a new column) that indicates Yes or No ( or True or False) whether a chemical has a corresponding guideline?

Thanks in advance