Two dates and USERELATIONSHIP

[okusai3000]

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!!

[vivran22 · Accepted solution]

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]

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

[v-juanli-msft]

Hi @okusai3000 

Could you show a simple example and expected result to make me clear?

Best Regards

Maggie