Forum Discussion
Advocate IICalculated function in Direct Query
I try to achieved this calculated column and work fine in Import Mode but In Direct Query it does not work for CALCULATE and REMOVEFILTERS. how could i possible to achieved it?
Test =
VAR CurrentDate = Workstation_HealthCheck[checked_at]
VAR CurrentIndex = Workstation_HealthCheck[id]
VAR PreviousDate =
CALCULATE (
MAX ( Workstation_HealthCheck[checked_at] ),
Workstation_HealthCheck[id] = CurrentIndex - 2,
REMOVEFILTERS ( Workstation_HealthCheck )
)
VAR TimeDifference =
DATEDIFF ( PreviousDate, CurrentDate, HOUR )
RETURN
IF (
ISBLANK ( PreviousDate ),
"UP", -- Or "First Check-in"
IF ( TimeDifference > 4, "DOWN", "UP" )
)
Hi,
This is a limitation of calculated columns in DirectQuery mode, rather than an issue with your DAX syntax. In DirectQuery, calculated columns are generally restricted to row-level expressions, and functions such as CALCULATE that modify filter context aren't supported in this scenario.
For your requirement, where you need to compare the current row with the row whose ID is CurrentIndex - 2, I would recommend one of these approaches.
Option 1: Calculate it in the source SQL query (recommended)
If your source is SQL Server, use LAG() or a self-join in a SQL view to retrieve the previous check-in date. For example, assuming id is sequential and the required previous row is exactly id - 2:
SELECT current_row.id, current_row.checked_at, previous_row.checked_at AS PreviousDate, CASE WHEN previous_row.checked_at IS NULL THEN 'UP' WHEN DATEDIFF( HOUR, previous_row.checked_at, current_row.checked_at ) > 4 THEN 'DOWN' ELSE 'UP' END AS TestFROM dbo.Workstation_HealthCheck AS current_rowLEFT JOIN dbo.Workstation_HealthCheck AS previous_row ON previous_row.id = current_row.id - 2;
Replace the table name with your actual source table. This reproduces your current logic, assuming id uniquely identifies each row and the previous record is identified by id - 2.
You can then use the Test column directly in Power BI while keeping the table in DirectQuery mode.
Option 2: Use a measure
If the result only needs to be displayed in visuals and doesn't need to be used as a physical column, a measure may be an option. Measures support more flexible filter-context operations, although the exact implementation depends on how rows are selected in the visual.
Option 3: Use Import mode for this table
If near-real-time data isn't essential, you could import this table and retain your original calculated-column logic.
My recommendation: Since this calculation looks at another row in the same table, implement it in a SQL view if possible. It avoids the DirectQuery calculated-column restriction and lets the database perform the comparison.
Hope this helps.
Thanks
2 Replies
- rajendraongole1
Super User
Hi yannaingoo - Just one additional point to consider: LAG() and a self-join using id - 2 may produce different results.
- LAG(checked_at, 2) retrieves the timestamp two rows earlier based on the specified ordering.
- A self-join using id = CurrentIndex - 2 retrieves the timestamp only when that exact ID exists.
If your requirement is to match the exact ID difference, the self-join approach is more appropriate.
Also, if the table contains multiple workstations, make sure the comparison is performed within the same workstation to avoid incorrect UP/DOWN statuses.
For DirectQuery, I would prefer calculating this at the source level rather than using a DAX calculated column. Hope this additional clarification helps!
- SamInogic
Hi,
This is a limitation of calculated columns in DirectQuery mode, rather than an issue with your DAX syntax. In DirectQuery, calculated columns are generally restricted to row-level expressions, and functions such as CALCULATE that modify filter context aren't supported in this scenario.
For your requirement, where you need to compare the current row with the row whose ID is CurrentIndex - 2, I would recommend one of these approaches.
Option 1: Calculate it in the source SQL query (recommended)
If your source is SQL Server, use LAG() or a self-join in a SQL view to retrieve the previous check-in date. For example, assuming id is sequential and the required previous row is exactly id - 2:
SELECT current_row.id, current_row.checked_at, previous_row.checked_at AS PreviousDate, CASE WHEN previous_row.checked_at IS NULL THEN 'UP' WHEN DATEDIFF( HOUR, previous_row.checked_at, current_row.checked_at ) > 4 THEN 'DOWN' ELSE 'UP' END AS TestFROM dbo.Workstation_HealthCheck AS current_rowLEFT JOIN dbo.Workstation_HealthCheck AS previous_row ON previous_row.id = current_row.id - 2;
Replace the table name with your actual source table. This reproduces your current logic, assuming id uniquely identifies each row and the previous record is identified by id - 2.
You can then use the Test column directly in Power BI while keeping the table in DirectQuery mode.
Option 2: Use a measure
If the result only needs to be displayed in visuals and doesn't need to be used as a physical column, a measure may be an option. Measures support more flexible filter-context operations, although the exact implementation depends on how rows are selected in the visual.
Option 3: Use Import mode for this table
If near-real-time data isn't essential, you could import this table and retain your original calculated-column logic.
My recommendation: Since this calculation looks at another row in the same table, implement it in a SQL view if possible. It avoids the DirectQuery calculated-column restriction and lets the database perform the comparison.
Hope this helps.
Thanks
