Hi, my Median() by patient - dax logic gives me incorrect result.
I need to get Median Time by Test
I need to get median time by patient. If multiple tests are found for a given patient - for selected period, use the latest “collect” or “receive date” (based on date type selection) of the record for the median computation.
Median Time by Test - Correct result
Median Time by test =
var med = MEDIAN(fact_detail[tat_time])
RETURN SWITCH (TRUE()
, [selected date type] = “Collect”, med — default active relationship
, [selected date type] = “Receive” , CALCULATE (med
, USERELATIONSHIP(dim_date[dim_date_id], fact_detail[rec_dim_date_id] ))
1- my Median Time by patient result is incorrect.
2- Dax query challenge, do not know how to force relationship (USERELATIONSHIP function) on getting max(collect) or max(receive) per patient based on Date Type slicer (see pic below).
Median Time by patient =
VAR vTable = SUMMARIZE(fact_detail, fact_detail[PatientIdentifier]
, “coll_dt” , MAX(fact_detail[coll_dt] )
VAR vFinal = NATURALINNERJOIN(vTable, fact_detail)
RETURN MEDIANX(vFinal, fact_detail[tat_time] )
Data model that I had set up:-
- active relationship -dim_date[dim_date_id] --> fact_detail[coll_dim_date_id] = collect
- non-active relationship -dim_date[dim_date_id] --> fact_detail[rec_dim_date_id] = receive