Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
27 | |
20 | |
15 | |
12 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |