Using distinct in iterator function?

[GoingIncognito]

Hi.

I want to count median time for each item to process through. I've a fact table that has items as one column, and creation date as one column and finally resolution date as third column. 
So I wrote the following: medianx(table, table[item], datediff(creation, resolution, day)). But as this is a fact table each item has several rows, how do I insert distinct(table[item]) into this? Tried few approaches to no avail. 

Thank you.

[AlB · Accepted solution]

@GoingIncognito 

1. Place Label in  a table visual

2. Place this measure in the table:

Measure =
MEDIANX (
    DISTINCT ( Table1[Key] ),
    VAR createdT_ = CALCULATE ( MIN ( Table1[Created] ) )
    VAR solvedT_ = CALCULATE ( MAX ( Table1[Resolution] ) )
    RETURN
        solvedT_ - createdT_ //Time diff in days...
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[GoingIncognito]

Oh, I thought I had it figured out. I guess I didn't. 
My data simplified is following:

Each key can have several rows. I'm interested averages time from created to resolution. So I'm not interested on updated column, but because it exists there is several rows for each key. And I prefer using median instead of average because the data isn't nearly normal distributed. In my report I'll group keys by labels, so I can show how time to resolution differs for each label.

Thank you!

[v-janeyg-msft]

Hi, @GoingIncognito 

I'd love to help you, but I really don't know what your calculation logic,Could you please share your desired result?So we can help you soon.

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.

[GoingIncognito]

If my intensios are this difficult to fathom then I must be really off the target. 
Okay, let's say that I want for each distinct label (please see the picture in my previous post) a median of how much all the tickets under that label took time from created to resolution.

E.g. let's say that under "Utilities label" I had only two tickets. First ticket took 7 days to be completed and second took 5 days.  As we have no clear midmost number we have to calculate it: (5+7)/2 = 6. And I want to do this for all the labels in my data. I thought iterator would be nice, because it would show the time from created to resolution on drill-down (this number of course wouldn't be a median but the value itself).

Am I still not making any sense?
And thanks for the help!

[v-janeyg-msft]

Hi, @GoingIncognito 

It’s my pleasure to answer for you.

According to your description,you want to count median time,but medianx function only count the number in the column,It seems somewhat contradictory.

Can you provide some sample data and expected results for reference in order to quickly solve your problem?

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.

[amitchandak]

@GoingIncognito , Try like , if this can work

medianx(values(table[item]), datediff(min(Table[creation]), max(Table[resolution]), day))

[GoingIncognito]

I get an error: A table of multiple values was supplied where a single value was expected.

[AlB]

Hi @GoingIncognito 

Plase show sample data and an example with expected result that helps clarify the requirements. Otherwise we'll waste time making assumptions about what you actually need

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers