Forum Discussion

danielmillion's avatar
danielmillion
Frequent Visitor
6 years ago
Solved

Calculating from Imported & Direct query

Hello, 

 

I have a powerapps app that allows for data input. This app is connected to SQL Server and updates data there.

I use direct query to retrieve data from that SQL Server as i want the most recent data at all times.

I have an imported table from our client's database (it is the G/L data).

 

What im trying to do is summing the G/L data with user's input, to present the total ammount of G/L value and user's input.

It works fine when i use a single measure :

Total Value =

//calculate the value in the G/L

CALCULATE(SUM('Imported G/L Table'[Value]))+

//calculate the value per month from the pivot structured input table

IF(MONTH(SELECTEDVALUE('Dates Table'[DateMonth]))=1,CALCULATE(SUM('Input Table'[jan])),
IF(MONTH(SELECTEDVALUE('Dates Table'[DateMonth]))=2,CALCULATE(SUM('Input Table'[feb])),
IF(MONTH(SELECTEDVALUE('Dates Table'[DateMonth]))=3,CALCULATE(SUM('Input Table'[mar])),
IF(MONTH(SELECTEDVALUE('Dates Table'[DateMonth]))=4,CALCULATE(SUM('Input Table'[apr])),
IF(MONTH(SELECTEDVALUE('Dates Table'[DateMonth]))=5,CALCULATE(SUM('Input Table'[may])),
IF(MONTH(SELECTEDVALUE('Dates Table'[DateMonth]))=6,CALCULATE(SUM('Input Table'[jun])),
IF(MONTH(SELECTEDVALUE('Dates Table'[DateMonth]))=7,CALCULATE(SUM('Input Table'[jul])),
IF(MONTH(SELECTEDVALUE('Dates Table'[DateMonth]))=8,CALCULATE(SUM('Input Table'[aug])),
IF(MONTH(SELECTEDVALUE('Dates Table'[DateMonth]))=9,CALCULATE(SUM('Input Table'[sep])),
IF(MONTH(SELECTEDVALUE('Dates Table'[DateMonth]))=10,CALCULATE(SUM('Input Table'[oct])),
IF(MONTH(SELECTEDVALUE('Dates Table'[DateMonth]))=11,CALCULATE(SUM('Input Table'[nov])),
IF(MONTH(SELECTEDVALUE('Dates Table'[DateMonth]))=12,CALCULATE(SUM('Input Table'[dec])),

//if date is not selected, retrieve the sum of all months to calculate total

CALCULATE(SUM('Input Table'[jan]))+
CALCULATE(SUM('Input Table'[feb]))+
CALCULATE(SUM('Input Table'[mar]))+
CALCULATE(SUM('Input Table'[apr]))+
CALCULATE(SUM('Input Table'[may]))+
CALCULATE(SUM('Input Table'[jun]))+
CALCULATE(SUM('Input Table'[jul]))+
CALCULATE(SUM('Input Table'[aug]))+
CALCULATE(SUM('Input Table'[sep]))+
CALCULATE(SUM('Input Table'[oct]))+
CALCULATE(SUM('Input Table'[nov]))+
CALCULATE(SUM('Input Table'[dec]))

))))))))))))

The problem with this measure is that it is PAINFULLY SLOW. A full minute to drill down through a table or bar chart.
I also tried to use UNION and SELECTCOLUMNS to merge them in a calculated table, and then just return the SUM of a column,
which is very very fast, BUT, it wont return the input data this way when clicking refresh on the power bi service refresh or chrome page refresh.


Dear community, is there any suggestion, something i did wrong, or any guidance you can give me ?
Thank you very much !
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    danielmillion ,

     

    You could modify the dax like pattern below:

    Total Value =
SUM ( 'Imported G/L Table'[Value] )
    + SWITCH (
        MONTH ( SELECTEDVALUE ( 'Dates Table'[DateMonth] ) ),
        1, SUM ( 'Input Table'[jan] )
    )

     

    Community Support Team _ Jimmy Tao

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

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    danielmillion Your data tables need to be unpivoted, it is weird that from backend data is showing up as pivoted, it this how data is stored? if you get unpivoted data and month and value on the rows and then it will be super fast and also follow the best practice.

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    6 years ago

    danielmillion ,

     

    You could modify the dax like pattern below:

    Total Value =
SUM ( 'Imported G/L Table'[Value] )
    + SWITCH (
        MONTH ( SELECTEDVALUE ( 'Dates Table'[DateMonth] ) ),
        1, SUM ( 'Input Table'[jan] )
    )

     

    Community Support Team _ Jimmy Tao

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