How to get result for best optimization?

Questions & Answers
1

Hi,

I got the result, but the formula works too long. Maybe there is a way to optimize.

001
001.png1881×894 54.3 KB

I tried to do something like this

CASE
WHEN
[Time].CurrentHierarchyMember.get(‘KEY’) = 7 OR
[Time].CurrentHierarchyMember.get(‘KEY’) = 8 AND
[Measures].CurrentHierarchyMember.Value = 0, And
[Time]… only current year (next year another value of (‘KEY’) = 7, and (‘KEY’) = 8)

… THEN get value from [Measures].[-Test-] if [Time].CurrentHierarchyMember.get(‘KEY’) = 6

but i don’t understand how i could write this formula.

Maybe you have some ideas how to get result for best optimization?

2

Hi @Erik1

It would be essential to understand how you defined the measure “-Test-” which is re-used in all other calculated measures.
Please share the code for this calculated measure:

Also, make sure you enabled “Nonempty” crossjoin

Martins / eazyBI

3

Hi @martins.vanags

-Test_:

IIf (DateCompare(Now(),[Time].CurrentHierarchyMember.NextStartDate) = -1

     OR (Month(Now())-1 = Month([Time].CurrentHierarchyMember.StartDate)
     AND Year(Now()) = Year([Time].CurrentHierarchyMember.StartDate) 
     AND Day(Now()) < 12), 

   [Measures].[Skirtumas tarp Pajamos (TAS) ir Pajamos (SF)  Filtras], 

   [Measures].[All-REV akum]

),

All-REV akum:

NonZero(
IIf( DateBetween([Time].CurrentHierarchyMember.StartDate,
[Measures].[EST-START From Descendants],
[Measures].[Due-date From Descendants]) OR
DateBetween([Time].CurrentHierarchyMember.NextStartDate,
[Measures].[EST-START From Descendants],
[Measures].[Due-date From Descendants]),
Sum(
{
PreviousPeriods([Time].CurrentHierarchyMember),
[Time].CurrentHierarchyMember
},
[Measures].[ALL-REV with due date]),
0))

EST-START From Descendants:

Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Issue].CurrentHierarchyMember.Get(‘Issue type ID’) = 10107).Item(0).Get(‘EST-START’)

Due-date From Descendants:

Filter(Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Issue].CurrentHierarchyMember.Get(‘Issue type ID’) = 10107).Item(0).Get(‘Due date’)

[Measures].[Skirtumas tarp Pajamos (TAS) ir Pajamos (SF) Filtras]:

To find that measure I use about 20 formulas, they all work pretty fast about 1-3 seconds. But when I use 001, 002, 003 to find Result its takes about 15 second, if it’s necessary I can send all formulas.

4

Hi,
After you made sure that “Nonempty” cross-join is enabled and “EST-START” custom field is imported as measures, you could update your calculated measures as follows:

EST-START From Descendants:

    Cache(
    Filter(
    Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
    [Issue].CurrentHierarchyMember.Get('Issue type ID') = 10107
    AND
    [Measures].[Issues with EST-START]>0
    ).Item(0).Get('EST-START')
    )

and Due-date From Descendants

Cache(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Issue].CurrentHierarchyMember.Get('Issue type ID') = 10107
AND
[Measures].[Issues with due date]>0
).Item(0).Get('Due date')
)

Since these two are actually used in all your calculated measures in columns.
But perhaps there is something more to improve behind "Skirtumas tarp Pajamos (TAS) ir Pajamos (SF) Filtras
" measure.

Martins / eazyBI team

5

Thank you very much for your reply. “Cache” helped me in other formula.