Seeking DAX Assistance for DirectQuery Mode in Power BI Tabular Model

[SamirPowerBI]

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.

[123abc]

In DirectQuery mode in Power BI, you have some limitations when it comes to creating calculated columns and using certain DAX functions. Since you cannot use functions like CALCULATE or MAXX in calculated columns, you'll need to find an alternative approach to achieve your goal efficiently.

One way to approach this problem is to create a measure instead of a calculated column, as you mentioned. Measures are computed on the fly and can be used to aggregate data. In your case, you want to count the number of rows that meet your specific condition.

Here's a suggested approach:

1. Create a measure to count the flagged rows based on your condition:

FlaggedRowsCount =
VAR CurrentRow = SELECTEDVALUE(Table[Row ID])
VAR CurrentArrival = SELECTEDVALUE(Table[Arrival])
VAR CurrentClient = SELECTEDVALUE(Table[Client])
VAR CurrentHospital = SELECTEDVALUE(Table[Hospital])

RETURN
COUNTROWS(
FILTER(
Table,
Table[Row ID] <> CurrentRow &&
Table[Client] = CurrentClient &&
Table[Hospital] = CurrentHospital &&
Table[Depart] >= CurrentArrival &&
Table[Depart] <= CurrentArrival + 2
)
)

In this measure, we use the SELECTEDVALUE function to get the values for the current row's Arrival, Client, and Hospital, and then we filter the table to count the rows that meet your specific condition within a 48-hour window.

1. Create a visual in your report and use the FlaggedRowsCount measure to display the count of flagged rows.

This approach should work efficiently in DirectQuery mode with a large dataset, as the measure is computed on the fly and doesn't require the creation of a calculated column. You can use this measure in visuals to display the count of flagged rows for each row in your table.

Please replace 'Table' with the actual name of your table, and 'Row ID' with the primary key or a unique identifier for each row in your table. This measure should give you the count of flagged rows based on your condition.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.