Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculations with cross filterings

Hi 

I have a scenario where I have al the hospital admissions in a table. Admissions that leads to a readmission is called "index admission". In my report I have the admission diagnosis and readmission diagnosis. I have managed to create cross filtering where when you select an index diagnosis , it filters the next table showing readmission diagnosis that index diagnosis lead to. Similarly, when you click on a readmission diagnosis, it filters the index diagnosis which leat to that readmission diagnosis. Please see mock-up report rrequirement and table relations below:

I cannot seem to come up with a measure that gives me the Total Admissions count that is marked in the picture above. 

(Admissions and Readmissions are the same table, I duplicated the Admissions table in order to create cross filtering)

Can anyone help me with this please? Many Thanks

Ilky

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi amitchandak

    I found the solution:

    Measure =
    VAR selected = SELECTEDVALUE(Admissions[ReadmissionID])
    RETURN
    CALCULATE(COUNTROWS(Readmissions),
    ALLCROSSFILTERED(Readmissions), Readmissions[AdmissionID]= selected)

     Thanks for your time to reply to me anyway 🙂

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , As readmission to admission, is one to many .

    Count(readmission[AdmissionID]) Or Count(Admission[AdmissionID])  should give you the count. 

     

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak

      I found the solution:

      Measure =
      VAR selected = SELECTEDVALUE(Admissions[ReadmissionID])
      RETURN
      CALCULATE(COUNTROWS(Readmissions),
      ALLCROSSFILTERED(Readmissions), Readmissions[AdmissionID]= selected)

       Thanks for your time to reply to me anyway 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak 

      Thans for reply, however plain COUNT() does not work. I also have another column which tells me type of the readmission (2, 7 or 28 days readmission). I did not include this in my original post because I believe it should not matter. All I want is to be able to calculate the total number of admissions per diagnosis filtered in the Readmissions table. 

      Due to the bi-directional relation setup, selecting index or readmission diagnosis must cross filter. 

      Unfortunatelly this is hopsital data and I need to cleanup a lot of columns to create a mock-up report. I will try 🙂

      Regards

      Ilky

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak , I try to explain verbally what needs to happen. In the picture I posted, I select Index Diagnosis E, which lead to readmission diagnosis B (3 times) and F(2 times). These counts are coming from the Readmissios table (my t-to-many relation between the tables handle that). Total admissions colum in the Readmissions (highlighted section) should come from either Admissions or Readmissions , ignoring all filters and should give me the count of admissionIDs for B and F.