Forum Discussion

TARUGOKING's avatar
TARUGOKING
Icon for Helper I rankHelper 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///

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
1 year ago

Hi,

Difficult to understand the data that you have pasted.  Share the download link of an MS Excel file with the data in one tab and expected result in another tab.