Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi,
I am new to statistical summary and want to calculate MIN, MAX, AVERAGE, PERCENTILE of count of patients visits using DAX. This will help me to show the distribution of visits by Patients, Doctors, Occupation and Visit Status, Visit Type and Day of the week. I already have a measure for count of visits which is basically COUNTROWS of the fact table "Visits". As I understand, MIN, MAX does not accept DAX measure and therefore I am stuck.
Following is the sample of my fact table "Visits".
Please help!!
Solved! Go to Solution.
Hi @Nsalvi ,
I modified my dax based on your description. please point out if I misunderstood you.
Count of Visits = COUNTA('Table'[Visit_Type_ID])
Min_visits = MINX(SUMMARIZE(ALLSELECTED('Table'),[Patient MRN],"visits",[Count of Visits]),[visits])
Max_visits = MAXX(SUMMARIZE(ALLSELECTED('Table'),[Patient Account Number],"visits",[Count of Visits]),[visits])
Avg_visits =
var _a= SUMX(SUMMARIZE(ALLSELECTED('Table'),'Table'[Patient MRN],"visit",[Count of Visits]),[visit])
var _b= CALCULATE(DISTINCTCOUNT('Table'[Patient MRN]),ALLSELECTED('Table'))
RETURN DIVIDE(_a,_b,0)
Column = COUNTROWS(FILTER('Table','Table'[Patient MRN]=EARLIER('Table'[Patient MRN])))
PERCENTILE.INC 0.25 = PERCENTILE.INC ([Column],0.25)
VisitsPercentile 25 = CALCULATE([PERCENTILE.INC 0.25],ALLSELECTED('Table'))
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Share data in a format that can be pasted in an MS Excel file. Show the expected result in a simple Table format.
Hi @Ashish_Mathur ,
Please find below sample data in table format:
Index | Date Of Service | Patient Account Number | Patient MRN | Appt_P_ID | Visit_Status_ID | Visit_Type_ID |
1 | 1/2/2018 | 120606 | 11044 | 1 | 1 | 1 |
2 | 1/2/2018 | 68797 | 11052 | 2 | 2 | 1 |
3 | 1/2/2018 | 73671 | 11064 | 2 | 2 | 1 |
4 | 1/2/2018 | 119974 | 11094 | 3 | 2 | 2 |
5 | 1/2/2018 | 66904 | 11096 | 4 | 2 | 2 |
6 | 1/2/2018 | 66904 | 11096 | 5 | 2 | 1 |
7 | 1/2/2018 | 29679 | 11128 | 6 | 2 | 1 |
8 | 1/2/2018 | 119183 | 11145 | 5 | 2 | 1 |
9 | 1/2/2018 | 61202 | 11213 | 2 | 2 | 1 |
10 | 1/2/2018 | 68429 | 11247 | 2 | 2 | 1 |
11 | 1/2/2018 | 118192 | 11284 | 5 | 2 | 1 |
12 | 1/2/2018 | 69435 | 11308 | 6 | 2 | 1 |
13 | 1/2/2018 | 30778 | 11319 | 2 | 2 | 1 |
14 | 1/2/2018 | 60029 | 11335 | 5 | 2 | 1 |
16 | 1/2/2018 | 112714 | 11347 | 3 | 2 | 2 |
15 | 1/2/2018 | 112714 | 11347 | 2 | 2 | 1 |
17 | 1/2/2018 | 122587 | 11405 | 5 | 2 | 1 |
18 | 1/2/2018 | 118894 | 11484 | 6 | 2 | 1 |
19 | 1/2/2018 | 118623 | 11492 | 2 | 2 | 1 |
20 | 1/2/2018 | 62513 | 11532 | 2 | 2 | 1 |
Following is the sample expected result:
Patient MRN | Total Visits | Min Visits | Max Visits | Average Visits | Percentile 25 |
11044 | |||||
11052 | |||||
11064 | |||||
11094 | |||||
11096 | |||||
11096 | |||||
11128 | |||||
11145 | |||||
11213 | |||||
11247 | |||||
11284 | |||||
11308 | |||||
11319 | |||||
11335 | |||||
11347 | |||||
11347 | |||||
11405 | |||||
11484 | |||||
11492 | |||||
11532 |
I would like to see the expected results by selecting any of the following categories:
Date of Service (Selected Range)
Patient MRN
Appt_P_ID
Visit_Status_ID
Visit_Type_ID
I hope this clarifies the matter.
Thank you for your help!
Hi,
What do you mean by min visits/max visits?
Hi @Ashish_Mathur ,
What I mean by min visits and max visits is that what is the minimum count of patients visits to the clinic and what was the maximum count of patients visits to the clinc.
I hope that helps!
Hi @Nsalvi ,
According to your description, here are my steps you can follow as a solution.
(1) This is my test data.
(2) We can create a calculated column.
Column = COUNTROWS(FILTER('Table','Table'[Patient Account Number]=EARLIER('Table'[Patient Account Number])))
(3) We can create measures.
Visits = COUNTA('Table'[Visit_Type_ID])
Min_visits = MINX(SUMMARIZE(ALLSELECTED('Table'),[Patient Account Number],"visits",[Visits]),[visits])
Max_visits = MAXX(SUMMARIZE(ALLSELECTED('Table'),[Patient Account Number],"visits",[Visits]),[visits])
Avg_visits = DIVIDE(SUMX(VALUES('Table'[Patient Account Number]),[Visits]),DISTINCTCOUNT('Table'[Patient Account Number]),0)
PERCENTILE.INC 0.25 = PERCENTILE.INC ([Column],0.25)
(4) Then the result is as follows.
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-tangjie-msft ,
Thank you for your reply with solution.
However, after creating calculated column and all the measures, I am getting following results which is incorrect.
The only change I have made in your measures is that I have used "Patient MRN" instead of "Patient Account Number" column.
Please help to understand why am I getting the above results.
Thank you!!
Hi @Nsalvi ,
I modified my dax based on your description. please point out if I misunderstood you.
Count of Visits = COUNTA('Table'[Visit_Type_ID])
Min_visits = MINX(SUMMARIZE(ALLSELECTED('Table'),[Patient MRN],"visits",[Count of Visits]),[visits])
Max_visits = MAXX(SUMMARIZE(ALLSELECTED('Table'),[Patient Account Number],"visits",[Count of Visits]),[visits])
Avg_visits =
var _a= SUMX(SUMMARIZE(ALLSELECTED('Table'),'Table'[Patient MRN],"visit",[Count of Visits]),[visit])
var _b= CALCULATE(DISTINCTCOUNT('Table'[Patient MRN]),ALLSELECTED('Table'))
RETURN DIVIDE(_a,_b,0)
Column = COUNTROWS(FILTER('Table','Table'[Patient MRN]=EARLIER('Table'[Patient MRN])))
PERCENTILE.INC 0.25 = PERCENTILE.INC ([Column],0.25)
VisitsPercentile 25 = CALCULATE([PERCENTILE.INC 0.25],ALLSELECTED('Table'))
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
118 | |
66 | |
65 | |
56 | |
50 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |