Forum Discussion

jenniferrayner's avatar
jenniferrayner
Frequent Visitor
3 years ago
Solved

Comparing Volume by Dates

I need to develop a visual to show the number of patients admitted compared to the number of patients discharged by month, year.  I have the admission and discharge dates in the same table, and curre...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Create a non-active relationship (Many to One and Single) from the Admission date to the Date column of the Calendar Table.  To your visual, drag Year and Month name from the Calendar Table.  Write these measures

    Discharged = distinctcount(Data[Patient ID])

    Admitted = calculate([discharged],userelationship(Data[Admission date],Calendar[date]))

    Hope this helps.

  • jenniferrayner's avatar
    jenniferrayner
    3 years ago

    I got it.  Needed to remove [Date] from the compelte date.

    Thank you!

Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

Create a non-active relationship (Many to One and Single) from the Admission date to the Date column of the Calendar Table.  To your visual, drag Year and Month name from the Calendar Table.  Write these measures

Discharged = distinctcount(Data[Patient ID])

Admitted = calculate([discharged],userelationship(Data[Admission date],Calendar[date]))

Hope this helps.

jenniferrayner's avatar
jenniferrayner
Frequent Visitor
3 years ago

Hi Ashish,

 

I appreciate the assistance.

When creating the Admitted measure, I'm getting the followng error: USERELATIONSHIP function can only use the two columns references participating in relationship.

 

Admitted Vol = CALCULATE('Disch Opt Data'[Discharged Vol],USERELATIONSHIP('Disch Opt Data'[Admit Date Time],'Date Table'[Complete Date].[Date]))
 
Any ideas?
  • jenniferrayner's avatar
    jenniferrayner
    Frequent Visitor
    3 years ago

    I got it.  Needed to remove [Date] from the compelte date.

    Thank you!

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      3 years ago

      You are welcome.