Forum Discussion

bdeleur's avatar
bdeleur
Icon for Helper III rankHelper III
3 years ago
Solved

Discharge department

Hi,   I want to make a report witch contains the following information. Out of a big data file I want to extract the follow up department. So I have department A and how many have a transfer (dis...
Help
  • tamerj1's avatar
    tamerj1
    3 years ago

    bdeleur 
    For now this is what I can provide you. The difference between Count 2 and Count 3 is that count 3 shall not count discharged from same department unless there is a discharge date. The difference shall not be noticed using the sample data as there are no blanks in the end date.

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi bdeleur 
Please refer to attached sample file with the proposed solution

Count = 
SUMX ( 
    VALUES ( 'Table'[Reference] ),
    VAR CurrentTable = CALCULATETABLE ( 'Table' ) 
    VAR FirstRecord = TOPN ( 1, CurrentTable, 'Table'[Start date], ASC )
    VAR LastRecord = TOPN ( 1, CurrentTable, 'Table'[Start date] )
    VAR FirstDepartment = MAXX ( FirstRecord, 'Table'[Department] )
    VAR LastDepartment = MAXX ( LastRecord, 'Table'[Department] )
    RETURN
        IF ( 
            FirstDepartment <> LastDepartment
                && MAX ( 'To'[Department] ) = LastDepartment
                && MAX ( 'FROM'[Department] ) = FirstDepartment,
            1
        )
  • bdeleur's avatar
    bdeleur
    Icon for Helper III rankHelper III
    3 years ago

    Hi tamerj1 ,

     

    Tnx. I will aply it to my data and see what the code makes of it. I will come back with the result.

  • bdeleur's avatar
    bdeleur
    Icon for Helper III rankHelper III
    3 years ago

    Hi,

     

    I applied the code and it seems to work fine. I have to do and check to the outcome to be sure. Just 1 thing is missing: the code doesn't count the patients who have a discharge out of hospital.

    How can we do that?

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      bdeleur 
      Please try

      Count =
SUMX (
    VALUES ( 'Table'[Reference] ),
    VAR CurrentTable =
        CALCULATETABLE ( 'Table' )
    VAR FirstRecord =
        TOPN ( 1, CurrentTable, 'Table'[Start date], ASC )
    VAR LastRecord =
        TOPN ( 1, CurrentTable, 'Table'[Start date] )
    VAR FirstDepartment =
        MAXX ( FirstRecord, 'Table'[Department] )
    VAR LastDepartment =
        MAXX ( LastRecord, 'Table'[Department] )
    VAR DischargeDate =
        MAXX ( LastRecord, 'Table'[End Date] )
    RETURN
        IF (
            AND ( FirstDepartment = LastDepartment, DischargeDate <> BLANK () )
                && MAX ( 'To'[Department] ) = LastDepartment
                && MAX ( 'FROM'[Department] ) = FirstDepartment,
            1
        )
)
      • bdeleur's avatar
        bdeleur
        Icon for Helper III rankHelper III
        3 years ago

        Now the matrix stays empty when I apply the code in your file.