DAX understanding - Patients per working day

[gianmarino]

Good day!

I am struggling big time with a measure I am trying to concoct for my business. I am running a diagnostics lab and we had, up until now, no operational KPIs.

I finally got my hands on a couple of queries that hopefully will shed some light on how we are doing and that associated to our strategy. One of these KPIs is patients per working day.

I have two databases, one with patients and another one with the working days per month.

Database 1 (HistExames)

 

Data (date): identifies the month as 1/1/YY. It is not useful as the effective date the client came to the lab but just as the month he did. Therefore, is an indicator of month, going from 1/1/2017 to 1/1/2018 (13 months).

Número (number): is akin to an order number. Each patient (new or repeat) will have a different order number. A uniquecount of número will give us total patients

Nome (name): this is the customer’s name. A uniquecount of this field will give us unique patients. A patient may have one or many orders, for instance, in jan-2017 patient “A” had an order for hematology profile and cholesterol test while in feb-2018 had another one for a glycemic curve.

Exame (test or procedure): contains the test in an order. As mentioned, one or many tests may be included in the same order number

Database 2 (DiasUteis)

I created the calendar table from scratch, basically from a Calendar (min, max) of my database 1. Then, I uniquecounted the data field and used a relate to another table to get the number of São Paulo’s working days (Brazil).

First thing I noted was that neither the calendar table nor the working days table (Database 2), are autodetected by the relationship manager. No biggie, I thought, so I manually created the one-to-many relationships between them.

The KPI I want to create must check the number of orders in a month and then divide it by the total number of working days of that month. I though that this would do:

ppd = DISTINCTCOUNT(HistExames[Número])/RELATED(DiasUteis[Duteis])

Nope. I get this error.

 

Obs: when using Related, no dropdown names appeared. I had to manually refer to the database and column of interest… to no avail.

Can you help me check my DAX (logic) ?

Thanks

Gian

[Zubair_Muhammad · Accepted solution]

@gianmarino

Hi...

RELATED is typically used in a calculated column....

Try using SUM(DiasUteis[Duteis])  or SELECTEDVALUE(DiasUteis[Duteis])

[gianmarino]

@Zubair_Muhammad, thanks! SUM did the trick!

Gian