Find the next row with earlier does not work with slicer

[wolfgang_voigt]

Hello guys,

I'm new to powerbi and I'm searching now already for hours for a (I think) easy problem.

I want to calculate a MTBF based on calls.

The calls can happen in different servicelines, which are filterable by a slicer.

I now simply want to display the time of the next call based on the selection in the slicer. In this screenshot everything is fine, as all categories are selected:

But if I now deselect one category the wrong date gets selected:

I'm using this code:

Next call = 
VAR NextRow = MINX(
	FILTER(
		ALLSELECTED(Events);
		Events[Time of call]>EARLIER(Events[Time of call];1)
	);
	Events[Time of call]
)
	
RETURN NextRow

I think the problem is, that the EARLIER function is not working when filtered with the slicer.

Can someone tell me how to fix this issue?

Thanks in advance!

Best regards

Wolfgang

[Anonymous · Accepted solution]

Hi @wolfgang_voigt 

This is because calculated colun wont respond to slicer changes as calculated column values will be calculated once the data load is done and it will remain static. Please use the logic in measure.

Thanks

Raj

[v-cherch-msft · Accepted solution]

Hi @wolfgang_voigt 

You may create a measure to get dynamic value.For example:

Next call Measure = 
VAR NextRow = MINX(
	FILTER(
		ALLSELECTED(Events);
		Events[Time of call]>MAX(Events[Time of call])
	);
	Events[Time of call]
)
	
RETURN NextRow

Regards,

Cherie

[v-cherch-msft · Accepted solution]

Hi @wolfgang_voigt 

You may create a measure to get dynamic value.For example:

Next call Measure = 
VAR NextRow = MINX(
	FILTER(
		ALLSELECTED(Events);
		Events[Time of call]>MAX(Events[Time of call])
	);
	Events[Time of call]
)
	
RETURN NextRow

Regards,

Cherie

[Anonymous · Accepted solution]

Hi @wolfgang_voigt 

This is because calculated colun wont respond to slicer changes as calculated column values will be calculated once the data load is done and it will remain static. Please use the logic in measure.

Thanks

Raj

[wolfgang_voigt]

Hi rajendran,

thanks for the hint didn't knew this behaviour and now it makes sense.

The reason I decided to use a coloumn was that I thought it would be more easy to calculate an average of that coloumn.

I now created the measure and try to find the average per category:

Uptime in hours = 
VAR NextRow =
    CALCULATE (
        MIN ( Events[Time of call] );
        FILTER (
            ALLSELECTED ( Events );
            Events[Time of call] > SELECTEDVALUE ( Events[Time of call] )
        )
    )	
VAR DDIFF = IF(ISBLANK(NextRow);
    Now() - SELECTEDVALUE ( Events[Time of call] );
    NextRow - SELECTEDVALUE ( Events[Time of call] )
)
RETURN int(DDIFF * 24)

The problem is, that I now don't have a coloumn and can not simply use the average function.

I guess I have to use AVERAGEX for that but it also requires a Table as Input and I don't get how I should select the measure here.

Maybe you can give a second tip here, I think this would help me alot!

Thanks!

-

Wolfgang

[wolfgang_voigt · Accepted solution]

I solved this on my own.

It was more easy than I thought:

Average MTBF in hours =
AVERAGEX(
    KEEPFILTERS(VALUES('Events'[Time of call]));
    CALCULATE([Uptime in hours])
)