Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Performance Dax measure

Hello Community, 

 

I'm working on a report and I have difficulties to show some Amounts. The problem is due to a measure that I have developed and it is causing the problem. below is the formula that I'm using : 

 
Amount = 
VAR Main_Account_V2=ALLEXCEPT(Transaction,Transaction[MainAccount],MainAccount[Libellé de compte],'Transaction DataArea'[Entité Juridique])
VAR Min_Date_V2=CALCULATE(MIN(Transaction[AccountingDate]),ALLSELECTED(Transaction))
VAR AmountV2=CALCULATE(SUM(Transaction[AccountingAmountDebit])-SUM(Transaction[AccountingAmountCredit]),Main_Account_V2,FILTER(ALL(Transaction[AccountingDate]),Transaction[AccountingDate]>=Min_Date_V2),FILTER(ALL(Transaction[Concat_ID]),Transaction[Concat_ID]<=MAX(Transaction[Concat_ID])))
Return
IF(AmountV2=BLANK(),[OpeningBalance],AmountV2+[OpeningBalance])
and it's doing this problem on my report. So I cant see the data on the table chart !!! (I circled it in yellow)
 

And when I deselect the display measure I can see what I am displaying. bellow the capture...

 

 Any ideas to solve this problem? Any solution to suggest?

 

Regards,

Thanks a lot.

Interesting Links
Need Help
Tips and Tricks
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    Hi, Anonymous 

     

    It’s my pleasure to answer for you.

    According to your description,Can you wait until this measure is loaded and the result is displayed, is it the correct result?

    Try like this:

     

    Amount =
VAR AmountV2 =
    CALCULATE (
        SUM ( Transaction[AccountingAmountDebit] )
            - SUM ( Transaction[AccountingAmountCredit] ),
        FILTER (
            ALLSELECTED ( Transaction ),
            Transaction[AccountingDate] >= MIN ( Transaction[AccountingDate] )
                && Transaction[Concat_ID] <= MAX ( Transaction[Concat_ID] )
        )
    )
RETURN
    IF ( AmountV2 = BLANK (), [OpeningBalance], AmountV2 + [OpeningBalance] )

     

    You can also split your measure into several, then use the performance analyzer to see which one takes the longer time, and then improve it.

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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

1 Reply

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    It’s my pleasure to answer for you.

    According to your description,Can you wait until this measure is loaded and the result is displayed, is it the correct result?

    Try like this:

     

    Amount =
VAR AmountV2 =
    CALCULATE (
        SUM ( Transaction[AccountingAmountDebit] )
            - SUM ( Transaction[AccountingAmountCredit] ),
        FILTER (
            ALLSELECTED ( Transaction ),
            Transaction[AccountingDate] >= MIN ( Transaction[AccountingDate] )
                && Transaction[Concat_ID] <= MAX ( Transaction[Concat_ID] )
        )
    )
RETURN
    IF ( AmountV2 = BLANK (), [OpeningBalance], AmountV2 + [OpeningBalance] )

     

    You can also split your measure into several, then use the performance analyzer to see which one takes the longer time, and then improve it.

    If it doesn’t solve your problem, please feel free to ask me.

     

    Best Regards

    Janey Guo

     

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