Forum Discussion

okusai3000's avatar
okusai3000
Helper IV
6 years ago
Solved

Two dates and USERELATIONSHIP

Hello everyone,

 

I'm getting nuts trying to solve the following.

 

  • I have a table with VISITS to PATIENTS. 
  • Each visit is related to a PATIENT_ID.
  • Could happen that you have 2 visits to the same patient and same month.
  • Each patient will be signing a form in one moment in time. This will be registered in another column as a date ([CON_VIS_CLI).

We need to know how many people signed the form each month and how many people have we seen in total in that month.

 

For the first requeriment, I solved it using:

 

Signed People=

CALCULATE(
DISTINCTCOUNT(INDICADORES_VISITAS[ID_PACIENTE]);
USERELATIONSHIP(INDICADORES_VISITAS[CON_VIS_CLI];Calendario[Date]))
 
Talking about the people that we have visited, and still have not signed, I was doing
[Signed People] + 
CALCULATE(
DISTINCTCOUNT(INDICADORES_VISITAS[ID_PACIENTE]);
USERELATIONSHIP(INDICADORES_VISITAS[CON_VIS_CLI]=BLANK()))
 
The problem with this is that once the patient has signed, he will be not counting anymore on that measure, meaning that the numbers will be moving with the time.
 
So, what I need to do is to get a Measure where I can grab, all the different PATIENTS_ID, from the VISITS we have done in that month, that their [CON_VIS_CLI] is BLANK OR HIGHER than the selected month.
 
In other words, If I'm checking the Visited Patients from January, and someone signed on March, HE SHOULD STILL APPEAR IN JANUARY AS HE DIDN'T SIGNED. Otherwise with my old Measure, he would have dissapeared.
 
Does anyone have an idea what should I do?
 
Millions of thanks!!
 

 

Need Help
  • vivran22's avatar
    vivran22
    6 years ago

    Hello okusai3000 

     

    Try this:

    Form Not Signed = 

VAR _SignFormDateChek = 
    FILTER(dtPatient,
    ISBLANK(dtPatient[[CON_VIS_CLI]) || dtPatient[[CON_VIS_CLI] > dtPatient[Vistit Date]
    )
    
VAR _RowCount = 
    CALCULATE(DISTINCTCOUNT(dtPatient[PATIENT_ID]),_SignFormDateChek
    )
RETURN
_RowCount

     

    Cheers!
    Vivek

    If it helps, please mark it as a solution
    Kudos would be a cherry on the top 🙂

    https://www.vivran.in/

    Connect on LinkedIn

3 Replies

  • vivran22's avatar
    vivran22
    Community Champion
    6 years ago

    Hello okusai3000 

     

    Try this:

    Form Not Signed = 

VAR _SignFormDateChek = 
    FILTER(dtPatient,
    ISBLANK(dtPatient[[CON_VIS_CLI]) || dtPatient[[CON_VIS_CLI] > dtPatient[Vistit Date]
    )
    
VAR _RowCount = 
    CALCULATE(DISTINCTCOUNT(dtPatient[PATIENT_ID]),_SignFormDateChek
    )
RETURN
_RowCount

     

    Cheers!
    Vivek

    If it helps, please mark it as a solution
    Kudos would be a cherry on the top 🙂

    https://www.vivran.in/

    Connect on LinkedIn

    • okusai3000's avatar
      okusai3000
      Helper IV
      6 years ago

      Sir, I simply LOVE YOU. 

       

      That was the solution.. Thank you so much.

       

       

      Form Not Signed = 

VAR _SignFormDateChek = 
    FILTER(dtPatient,
    ISBLANK(dtPatient[[CON_VIS_CLI]) || dtPatient[[CON_VIS_CLI] >= dtPatient[Vistit Date]
    )
    
VAR _RowCount = 
    CALCULATE(DISTINCTCOUNT(dtPatient[PATIENT_ID]),_SignFormDateChek
    )
RETURN
_RowCount