Am trying to compute a count of clients with a valid_VL_1 as follows;
For a VL to be valid, it should be;
1. Have an order_date >-12 months from the selected month in the slicer.
2. Clients should have a deduplicated TX_Curr_lab=1.
3. The exit date> client visit date.
Each client has numerous visits and thus this dataset contains many duplicates.
1. Before I remove the duplicate, I need to select dates <= the selected dates in the slicer,
2. Then sort the data in descending order based on 3 columns i.e patient_id, mflcode and visit_date
3. Then select the most recent date.
4. Select if the visit_date-12 months from the selected month in the slicer "As Valid_VL" else "Not Valid_VL"
5. Then ensure each client is active as at the selected date on the slicer i.e (Their Next_appointment date>-28 days of the selected date in the slicer.
Below is the DAX I did.
However, when I plot Tx_curr_lab and Valid_VL_1, the numbers are the same. However, I expect the Valid_VL_1 < TX_Curr_lab since TX_Curr_lab is the denominator.
Valid_VL_1 =
VAR curdate = MAX(CalenderDate[dates])
VAR curdate2=MAXX(DATEADD(CalenderDate[dates],-28,DAY),CalenderDate[dates])
Var date_lab_ordered=MAXX(Lab_data,Lab_data[date_ordered])
VAR valid=IF(date_lab_ordered>MAXX(DATEADD(CalenderDate[dates],-12,MONTH),CalenderDate[dates]),"Valid","Not Valid")
RETURN
COUNTX( FILTER( ADDCOLUMNS( SUMMARIZE(Lab_data,Lab_data[Greencard.patient_id],Lab_data[Greencard.mflcode],"vl_curr2", CALCULATE(COUNT(Lab_data[Greencard.patient_id]),Lab_data[Greencard.visit_date] <= curdate && Lab_data[Greencard.visit_date] <= Lab_data[Lab_ExitDate1] && Lab_data[Greencard.next_appointment_date] > curdate2) ), "vl_curr3", IF([tx_curr2] > 0, 1, 0) ), [vl_curr3] > 0 && valid="Valid" && [TX_Curr_lab]=1 ),valid)
