Forum Discussion

TARUGOKING's avatar
TARUGOKING
Helper I
1 year ago
Solved

Help on how to Generate These Measures

- My dataset consists of records from 2024 and 2025 - PatientID is unique (like SSN). - I'm using a TABLE visuailization - It has 2 slicers {YEAR] and [MONTH]. Can only pick 1 YEAR and 1 MONTH - ...
Need Help
Show and Tell
  • kpost's avatar
    kpost
    1 year ago

    Alright, here's your DAX for NewAdmission, ReAdmission and Total_Admissions

    I was a little unsure how you wanted to handle the situation where the same patient comes in more than once on the same day, especially if that's the first time the patient has been seen.

    So the way I did it, if a patient is seen for the first time on a certain day and they go in more than once on that day, ONE will count as an admission, and the rest will count as a re-admission.

     

    Also, if the same patient is seen twice in one month, they both will count. (Potentially one admission and one readmission, or both readmission, or whatever the case may be).


    First, you need to add an index column in Power Query (It's easy just select New column and at the top click Add Index Column).


    NewAdmission =

    VAR ranktable =
        ADDCOLUMNS (
            'sample',
            "first_Date", CALCULATE ( MIN ( 'sample'[FullDate] ), ALLEXCEPT ( 'sample', 'sample'[PatientID] ) ),
            "DayRank", RANKX (
                FILTER (
                    'sample',
                    'sample'[PatientID] = EARLIER ( 'sample'[PatientID] )
                        && 'sample'[FullDate] = EARLIER ( 'sample'[FullDate] )
                ),
                'sample'[Index],,
                ASC,
                Dense
            )
        )
    VAR result =
        SUMX (
            FILTER (
                ranktable,
                'sample'[FullDate] = [first_Date]
                && [DayRank] = 1
            ),
            [AdmitCount]
        )
    RETURN
        COALESCE(result, 0)

     

     

    ReAdmissions =

    VAR ranktable =
        ADDCOLUMNS (
            'sample',
            "first_Date", CALCULATE ( MIN ( 'sample'[FullDate] ), ALLEXCEPT ( 'sample', 'sample'[PatientID] ) ),
            "DayRank", RANKX (
                FILTER (
                    'sample',
                    'sample'[PatientID] = EARLIER ( 'sample'[PatientID] )
                        && 'sample'[FullDate] = EARLIER ( 'sample'[FullDate] )
                ),
                'sample'[Index],,
                ASC,
                Dense
            )
        )
    VAR result =
        SUMX (
            FILTER (
                ranktable,
                'sample'[FullDate] > [first_Date]
                || ( 'sample'[FullDate] = [first_Date] && [DayRank] > 1 )
            ),
            [AdmitCount]
        )
    RETURN
        COALESCE(result, 0)

     


    total_admissions = [NewAdmission] + [ReAdmissions]


    Then, to filter the dates in the table, you need to create this measure:

     

    Table_Filter =
        VAR year = SELECTEDVALUE(DateDim[YEAR])
        VAR MONTH = SELECTEDVALUE(DateDim[Month_Num])
        VAR START_OF_MONTH  = DATE(year, MONTH, 1)
       
        VAR END_OF_MONTH = EOMONTH(START_OF_MONTH, 0)
        var selected_date = SELECTEDVALUE('sample'[FullDate])
       
        RETURN
            IF ( selected_Date >= START_OF_MONTH && selected_date <= end_of_month, 1, 0)

     

    And you need to add this as a visual-level filter, only include rows where it equals 1.

    Attached is the .pbix file.

     

    ///MEDIOCRE POWER BI ADVICE, BUT IT'S FREE///

kpost's avatar
kpost
Solution Sage
1 year ago

I was able to create PatientAdmitCount, but I need more information on what "new Admissions" and "ReAdmissions" means.  Can you explain how those are calculated and what your expected values would be for a given selection?

 

Here's your solution so far.

 

Create a date table that is not connected to your Fact table containing patient data.

 

DateDim = CALENDAR(DATE(2024,1,1), DATE(2025,12,31))
 
In that table create these columns:
 
Month_Num = MONTH(DateDim[Date])
YEAR = YEAR(DateDim[Date])
 
Then add slicers for each column with 'Single Select' turned on to your page.
 
And here's the measure for PatientAdmitCount, such that the patient count will be the number of admits between 1/1/2024 and the end of your selected Year/Month.
 
PatientAdmitCount =
    var start_Date = DATE(2024,1,1)
    var year = selectedvalue(DateDim[YEAR])
    var month = selectedvalue(DateDim[Month_Num])
    var end_Date = DATE(year, month, DAY(EOMONTH(DATE(year, month, 1), 0)))

RETURN
CALCULATE(COALESCE(SUM('sample'[AdmitCount]), 0), 'sample'[FullDate] >= start_Date, 'sample'[FullDate] <= end_date)
 
 
 Attached is the .pbix file.
 
 
 

 

 

 

 

 

TARUGOKING's avatar
TARUGOKING
Helper I
1 year ago

Thank you. Here's additional info:

 

Using the initial dataset, with slicer set for APR 2025, my (1) [TotalAdmissions] is just the sum of the DS.[AdmitCount] per day.

 

The [NewAdmission] and [ReAdmission] just evaluates if the admitted patient for the MONTH+YEAR is a new patient or an old patient. 

 

[NewAdmission] + [ReAdmission] = [TotalAdmissions]

 

So for [ReAdmission], lets take the APRIL 4 as an example.

 

BANANA was admitted on 4/4/25. He is considered a ReAdmission because he had a prior admission record on 7/8/2024 (total admission record >1). 

 

 

So for [NewAdmission], lets take the APRIL 4/8 as an example.

 

MELON was admitted on 4/8/25. He is considered a NewAdmission because he had no prior admission record (total admission record =1).

 

 

 

  • kpost's avatar
    kpost
    Solution Sage
    1 year ago

    Alright, here's your DAX for NewAdmission, ReAdmission and Total_Admissions

    I was a little unsure how you wanted to handle the situation where the same patient comes in more than once on the same day, especially if that's the first time the patient has been seen.

    So the way I did it, if a patient is seen for the first time on a certain day and they go in more than once on that day, ONE will count as an admission, and the rest will count as a re-admission.

     

    Also, if the same patient is seen twice in one month, they both will count. (Potentially one admission and one readmission, or both readmission, or whatever the case may be).


    First, you need to add an index column in Power Query (It's easy just select New column and at the top click Add Index Column).


    NewAdmission =

    VAR ranktable =
        ADDCOLUMNS (
            'sample',
            "first_Date", CALCULATE ( MIN ( 'sample'[FullDate] ), ALLEXCEPT ( 'sample', 'sample'[PatientID] ) ),
            "DayRank", RANKX (
                FILTER (
                    'sample',
                    'sample'[PatientID] = EARLIER ( 'sample'[PatientID] )
                        && 'sample'[FullDate] = EARLIER ( 'sample'[FullDate] )
                ),
                'sample'[Index],,
                ASC,
                Dense
            )
        )
    VAR result =
        SUMX (
            FILTER (
                ranktable,
                'sample'[FullDate] = [first_Date]
                && [DayRank] = 1
            ),
            [AdmitCount]
        )
    RETURN
        COALESCE(result, 0)

     

     

    ReAdmissions =

    VAR ranktable =
        ADDCOLUMNS (
            'sample',
            "first_Date", CALCULATE ( MIN ( 'sample'[FullDate] ), ALLEXCEPT ( 'sample', 'sample'[PatientID] ) ),
            "DayRank", RANKX (
                FILTER (
                    'sample',
                    'sample'[PatientID] = EARLIER ( 'sample'[PatientID] )
                        && 'sample'[FullDate] = EARLIER ( 'sample'[FullDate] )
                ),
                'sample'[Index],,
                ASC,
                Dense
            )
        )
    VAR result =
        SUMX (
            FILTER (
                ranktable,
                'sample'[FullDate] > [first_Date]
                || ( 'sample'[FullDate] = [first_Date] && [DayRank] > 1 )
            ),
            [AdmitCount]
        )
    RETURN
        COALESCE(result, 0)

     


    total_admissions = [NewAdmission] + [ReAdmissions]


    Then, to filter the dates in the table, you need to create this measure:

     

    Table_Filter =
        VAR year = SELECTEDVALUE(DateDim[YEAR])
        VAR MONTH = SELECTEDVALUE(DateDim[Month_Num])
        VAR START_OF_MONTH  = DATE(year, MONTH, 1)
       
        VAR END_OF_MONTH = EOMONTH(START_OF_MONTH, 0)
        var selected_date = SELECTEDVALUE('sample'[FullDate])
       
        RETURN
            IF ( selected_Date >= START_OF_MONTH && selected_date <= end_of_month, 1, 0)

     

    And you need to add this as a visual-level filter, only include rows where it equals 1.

    Attached is the .pbix file.

     

    ///MEDIOCRE POWER BI ADVICE, BUT IT'S FREE///