Seeking DAX Assistance for DirectQuery Mode in Power BI Tabular Model
Hello Experts, I am working with a Power BI tabular model in DirectQuery mode and facing a challenge with DAX calculations. My model has a table with the following columns: Arrival (DateTime) Depart (DateTime) Client (Text) Hospital (Text) I need to create a calculated column that flags rows based on a specific condition: For each row, if the same client arrives at the same hospital within 48 hours of their last visit's departure time, the row should be flagged. Here are the specific requirements: I cannot pre-sort the data before loading it into the model. The DAX calculation needs to work efficiently in DirectQuery mode with a large dataset. Some of the functions typically used for such calculations (like CALCULATE, MAXX, etc.) are not supported in DirectQuery mode (for calculated columns) hence cannot use then directly. I am trying to create a Calculated Column (Flag) for repesentations and later will create a measure to add up this calculated field. Since I can't use the CALCULATE, MAXX I tried using below but still in vain as I cannot use a PLACEHOLDER function in calculate columns . VAR currentDate = Table[Arrival Date] VAR currentClient = Table[ClientID] VAR currentHospital = Table[HospitalID] RETURN SELECTCOLUMNS ( TOPN ( 1, FILTER ( ALL ( Table ), 'Table '[Client Depart DateTm] < currentDate && 'Table '[ClientID] = currentClient && 'Table '[HospitalID] = currentHospital ), 'Table '[Client Depart DateTm], DESC ), "Max Client Depart DateTm", 'v_FACT_ED_SE'[Client Depart DateTm] ) Could someone suggest an approach or DAX pattern that could achieve this in DirectQuery mode without compromising performance significantly? Or will it be useful to directly create measure and how ? I appreciate any guidance or advice you can offer.