Forum Discussion

Rajiv1237's avatar
Rajiv1237
Icon for Resolver I rankResolver I
9 years ago
Solved

Running total through direct query mode in PowerBI

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 :(   Run...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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's avatar
    Rajiv1237
    9 years ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
Rajiv1237
Icon for Resolver I rankResolver I
9 years ago

We can't apply this in Power BI.

 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      Rajiv1237
      Icon for Resolver I rankResolver I
      9 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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