Running total through direct query mode in PowerBI

[Rajiv1237]

I can't find a way to calculate a running total, without using FILTER. Filter is not supported in PowerBi direct query-mode.

All help is appreciated

Not supported in Direct Query Mode 😞

Running Total in DAX = CALCULATE( SUM('Table'[QTY]),
    FILTER(
        ALLSelected('Table'),
        'Table'[Date] <= MAX('Table'[Date])
    )
) 

[v-shex-msft · Accepted solution]

Hi @Rajiv1237,

Did you turn on the "allow unrestricted measures in directquery mode" option? If not you can turn on it first.

In addition, my formual is a measure, don't put it in calculate column.

Regards.

Xiaoxin Sheng

[v-shex-msft]

Hi @Rajiv1237,

You can use power query to calculate the running total at query editor side.

For detailed information you can take a look at below blog:

Create Running Totals in Power Query

Regards,

Xiaoxin Sheng

[Rajiv1237]

We can't apply this in Power BI.

We should find some simple solution to get running total in Power BI direct query mode.

[v-shex-msft]

Hi @Rajiv1237,

I test with below measure at direct query mode, it works smooth, perhaps you can try it.

Running Total = SUMX(FILTER(ALL(Table),Table[Date]<=MAX(Table[Date])),[QTY])

Regarsd,

Xiaoxin Sheng

[Rajiv1237]

Its DirectQuery Mode not Loading the data into PowerBI.

Filter function doesn't work in PowerBI Direct Query Mode

Filter function doesn't work in Direct Query Mode

[v-shex-msft · Accepted solution]

Hi @Rajiv1237,

Did you turn on the "allow unrestricted measures in directquery mode" option? If not you can turn on it first.

In addition, my formual is a measure, don't put it in calculate column.

Regards.

Xiaoxin Sheng

[Rajiv1237 · Accepted solution]

RunningTotalinPowerBI = SUMX(FILTER(ALLSELECTED(YearQty), YearQty[DateKey]<=MAX(YearQty[DateKey])), YearQty[Qty])

Filter works in Direct Query Mode: We have to check the option in Power BI desktop ‘Allow unrestricted measures in DirectQuery mode’

File -> Option and settings -> Option