topic Re: Percentage Positive by Date in DAX Commands and Tips

Percentage Positive by Date

Anonymous — Fri, 21 Aug 2020 15:37:49 GMT

Hello,

I would like to create a DAX measure that calculates the positiviy rate by date. Positivity Rate=Positive Cases per day/Total Tests Performed per day

My table has the following headers:

Result 2 is where the data is located that I wish to describe.

Draw date is the date I wish to use for the calculation.

Could you help me write a DAX that I can use to count the number of tests performed in a day as well as the number of positive tests that were also reported in that day?

Thanks

I have the following relationship table formed:

Re: Percentage Positive by Date

jthomson — Fri, 21 Aug 2020 16:05:07 GMT

Well, I'm assuming that measure "positive cases" is counting up something in the raw data based on some criteria that indicates that a case is positive. Make another measure that's basically the same except without that criteria, then another dividing one by the other

Re: Percentage Positive by Date

amitchandak — Fri, 21 Aug 2020 16:13:18 GMT

Anonymous , Not very clear with model.

You have to try like

divide(calculate(countrows(RawData), filter(resultlist, resultlist[result] ="Positive")) ,calculate(countrows(RawData)))

Re: Percentage Positive by Date

Anonymous — Fri, 21 Aug 2020 16:15:59 GMT

For the total number of tests performed your measure should count the total rows of patients tested which would look something like this:

[Total Tests] =
CALCULATE(DISTINCTCOUNT('Raw Data'[Patient ID]), FILTER('Raw Data', 'Raw Data'[Patient vs Non-Patient] = "Patient")

This measure counts the unique number of patients ID's in your data, filtering to make sure they are a patient (assuming you want patients only).

For positivity rate it would be something like this:

[Positivity Rate] =
VAR _positive = CALCULATE([Total Tests], FILTER('Raw Data', 'Raw Data'[Result 2] = "Positive")
VAR _result = DIVIDE(_postive, [Total Tests])
RETURN _result

This measure uses variables to calculate the positivity rate. It uses the same measure for total tests but adds an additional filter to the data so that we only count the positive results. In this instance I'm using it as a variable but you can use the same formula for another measure to get the # of positive cases.

These measures rely on the filter context of visuals. That means when you use the 'Date Table'[Date] column, it will give you the numbers for that specific date.