My measure can only work if I display the fields it uses

[Anonymous]

Hello, I have already posted a message on the first part of my problem. The solution that was given worked for me, it was exactly the answer I had asked for.

The problem is that, at the time I didn't realise doing what I wanted will cause a new problem.

My original post can be found here: By row, search for a value in another row of the same table

I have a measure that allows me to calculate the duration between the transition date of my current row, and the transition date of the previous transition.

If I display all fields in a table, the measure works just perfectly.

The problem is that if I want to create a visualization of my measure by Status, for example, it won't work because the required fields are not part of the visualization.

Id	Transitionned	From Status	To Status	Issue Id	Created	EXPECTED Duration
178244	2017-01-13 9:41	Open	In Analysis	26496	2017-01-11 13:11	2
178245	2017-01-13 9:41	In Analysis	To Do	26496	2017-01-11 13:11	0
180005	2017-01-20 9:46	Open	In Analysis	26566	2017-01-13 16:29	7
180006	2017-01-20 9:47	In Analysis	To Do	26566	2017-01-13 16:29	0
202703	2017-04-06 14:31	To Do	Validation	26566	2017-01-13 16:29	76
202706	2017-04-06 14:33	Validation	To Do	26566	2017-01-13 16:29	0
209420	2017-04-28 16:38	To Do	In Progress	26566	2017-01-13 16:29	22
209421	2017-04-28 16:38	In Progress	Validation	26566	2017-01-13 16:29	0
209444	2017-04-28 16:48	Validation	In Progress	26566	2017-01-13 16:29	0
211150	2017-05-04 15:53	In Progress	Done	26566	2017-01-13 16:29	6
211151	2017-05-04 15:53	Done	Validation	26566	2017-01-13 16:29	0
213229	2017-05-10 17:09	Validation	Done	26566	2017-01-13 16:29	6
215395	2017-05-18 13:57	To Do	Done	26496	2017-01-11 13:11	125
216558	2017-05-24 13:38	Done	Validation	26496	2017-01-11 13:11	6
216691	2017-05-24 16:20	Validation	Done	26496	2017-01-11 13:11	0
234580	2017-08-04 15:51	Open	Development	30681	2017-08-02 17:35	2
234581	2017-08-04 15:51	Development	Compilation	30681	2017-08-02 17:35	0
234805	2017-08-07 9:44	To Do	Done	30712	2017-08-07 8:28	0
235447	2017-08-08 15:41	Compilation	QA Pre-Production	30681	2017-08-02 17:35	4
235910	2017-08-10 17:22	QA Pre-Production	QA Production	30681	2017-08-02 17:35	2
235915	2017-08-10 17:25	QA Production	Done	30681	2017-08-02 17:35	0

Here's the measure that I used : 

Duration (d) = 
	VAR PreviousTransitions = 
		FILTER(
			ALLEXCEPT(
				'JIRA Changelogs',
				'JIRA Changelogs'[Issue Id]
			),
			'JIRA Changelogs'[Id] < SELECTEDVALUE('JIRA Changelogs'[Id]) 
		)
	VAR PreviousStepTime =
		CALCULATE(
			MAX('JIRA Changelogs'[Transitionned]),
			PreviousTransitions
		)
	RETURN
		DATEDIFF(
			IF(
				ISBLANK(PreviousStepTime), 
				SELECTEDVALUE('JIRA Changelogs'[Created]), 
				PreviousStepTime
			),
			SELECTEDVALUE('JIRA Changelogs'[Transitionned]),
			DAY
		)

Please find attached a sample pbix file with the expected result. I don't need to use a measure, if there's a better solution by adding a column, it's okay with me.

Thanks a lot in advance, your help is much appreciated!

[WolfBiber]

Hey,

are you trying to visualize timedurations ?

Have a look to "events in progress"

A good starting point is here:

https://community.powerbi.com/t5/Desktop/Count-of-Active-Contracts-by-Start-en-End-date/td-p/94104

Greetings,

Wolf

[Zubair_Muhammad · Accepted solution]

Hi @Anonymous

Try this calculated column. This is equivalent of the MEASURE above

Duration (c) =
VAR PreviousTransitions =
    FILTER (
        ALLEXCEPT ( 'JIRA Changelogs', 'JIRA Changelogs'[Issue Id] ),
        'JIRA Changelogs'[Id] < EARLIER ( 'JIRA Changelogs'[Id] )
    )
VAR PreviousStepTime =
    CALCULATE ( MAX ( 'JIRA Changelogs'[Transitionned] ), PreviousTransitions )
RETURN
    DATEDIFF (
        IF (
            ISBLANK ( PreviousStepTime ),
            'JIRA Changelogs'[Created],
            PreviousStepTime
        ),
        'JIRA Changelogs'[Transitionned],
        DAY
    )

[Anonymous]

Thanks @Zubair_Muhammad, that works perfectly. That's amazing how simple it was. I was able to use the Created value that is in another table by using RELATED.

That's perfect !

Thanks again, that was quick and very efficient !!

[Zubair_Muhammad]

@Anonymous