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