DAX formula crashes current Power BI Desktop Version

[ppvinsights]

Hi,

I have a table with telephon calls logs. If have asterix verbs for "PAUSE" and "UNPAUSE". I want to calculate the pause times.

The table has columns:

- index (unique, ascending)

- queue (type of queue - not that relevant)

- agent (the current agent)

The problem is, that for an "PAUSE" I have to look for the next "UNPAUSE" for the same agent, the same queue and an the smallest index bigger than the current one. So my formular is:

PauseNextIndex =
IF (
    FaktPascomQueueLog[verb] = "PAUSE";
    VAR curIdx = FaktPascomQueueLog[Index]
    VAR curAgent = FaktPascomQueueLog[agent]
    VAR curQueue = FaktPascomQueueLog[queue]
    RETURN
        MINX (
            FILTER (
                FaktPascomQueueLog;
                FaktPascomQueueLog[Index] > curIdx
                    && FaktPascomQueueLog[agent] = curAgent
                    && FaktPascomQueueLog[queue] = curQueue
            );
            FaktPascomQueueLog[Index]
        );
    BLANK ()
)

I elimated "EARLIER" with variables. Power BI crashes. It tells "we aare working on it" and never come back. We talk about 130.000 rows - I solved similar probloems with a lot more rows....

Any hints?

Thanks Holger

[Vera_33]

Are we talking about the Calculated Column? If calculate time, is Index a must? Thinking a different way, when "UNPAUSE", go back to find the previous "PAUSE" and calculate the time difference...

PrevPauseIndex =

VAR curIdx = FaktPascomQueueLog[Index]

RETURN

IF(FaktPascomQueueLog[verb] ="UNPAUSE",

CALCULATE(MAX(FaktPascomQueueLog[Index]), FILTER(FaktPascomQueueLog,FaktPascomQueueLog[Index]<curIdx)))

[v-easonf-msft]

Hi , @ppvinsights 

If help ,try measure as below:

PauseNextIndex =
IF (
    FaktPascomQueueLog[verb] <> "PAUSE";
    BLANK ();
    VAR i = FaktPascomQueueLog[Index]
    VAR a = FaktPascomQueueLog[agent]
    VAR q = FaktPascomQueueLog[queue]
    RETURN
        CALCULATE (
            MIN ( FaktPascomQueueLog[Index] );
            FaktPascomQueueLog;
            FaktPascomQueueLog[agent] = a;
            FaktPascomQueueLog[queue] = q;
            FaktPascomQueueLog[verb] = "UNPAUSE";
            FaktPascomQueueLog[Index] > i
        )
)

Best Regards,
Community Support Team _ Eason
 

[Mariusz]

Hi @ppvinsights 

If @v-easonf-msft  solution doesn't work, would you mind to create a sample with the column that you expect as a result included?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[ppvinsights]

Hi @Mariusz and @v-easonf-msft ,

thanks for the answers. I made an own dataset without any customer information and tried to change the formula like in your posts. But no difference. It is the March 2020 Version of PBI. I have several problems - like that PBI crashes several times per day - it just freezes... But anyway - let us stay to my current problem 🙂

I attached the datamodel to this post - perhaps it is running on your machine? (but I have a 32GB laptop - and the memory is not sufficient....cant believe that...)

https://www.dropbox.com/s/bl4nuayof5vl3ts/PBIPerformanceProblem.zip?dl=0

Thanks for your help!

Best 

Holger

[Mariusz]

Hi @ppvinsights 

Try this, if still too slow we can try Power Query

PauseNextIndex = 
IF (
    FaktPascomQueueLog[verb] = "PAUSE";
    VAR curIdx = FaktPascomQueueLog[Index] 
    RETURN
    CALCULATE(
        MAX( FaktPascomQueueLog[Index] );
        ALLEXCEPT( 
            FaktPascomQueueLog; 
            FaktPascomQueueLog[agent]; FaktPascomQueueLog[queue] );
        FaktPascomQueueLog[Index] > curIdx;
        FaktPascomQueueLog[verb] = "UNPAUSE"
    );
    BLANK ()
)

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Mariusz · Accepted solution]

Hi @ppvinsights 

Please see the attached file with a solution in Power Query

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[ppvinsights]

Thanks a lot - I am little bit confused, why this calculation is to hard for Power BI... But ok 🙂

[Mariusz]

Hi @ppvinsights 

No problem, it is considered best practice to do your column, table transformations in Power Query, in your case the number of rows and the type of the calculation probably was hard on the engine.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[ppvinsights]

Hi Mariusz,

thanks a lot for the answer.

But: Same result. I isolated the problem to a new DataModel (just to be sure, that the other data is not the memory problem).

We talk about 140.000 Lines. While adding the new column I am capturing the msmdsrv.exe. It is growing, growing, growing. And it stops after requesting all free memory (around 25 GB).

I already did this query and I already workes with the results. I know we are talking about an exponential problem. But I do not see any other possibility to solve my Problem - because there is no common property for a "PAUSE" and an "UNPAUSE" property ("UNPAUSE" is wrong anyway - there are more verbs which stops "PAUSE"). I only have the property "Agent", QUEUE" and an time-Index to find the next Log-Entry....

So: Any clue why this is not working in PBI? Any hints to solve that in antother way?