The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
Hi...
RELATED is typically used in a calculated column....
Try using SUM(DiasUteis[Duteis]) or SELECTEDVALUE(DiasUteis[Duteis])
Hi...
RELATED is typically used in a calculated column....
Try using SUM(DiasUteis[Duteis]) or SELECTEDVALUE(DiasUteis[Duteis])
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
141 | |
109 | |
107 | |
75 | |
61 |
User | Count |
---|---|
276 | |
129 | |
124 | |
100 | |
89 |