topic Re: Calculating median of time by group in DAX Commands and Tips

Calculating median of time by group

Anonymous — Fri, 10 Dec 2021 15:38:28 GMT

Hi.

Im having trouble in calculating the median of a column in my proyect. I can't get it to work (no result).

Well, the thing it's like this.

I have 2 tables, one it's a calendar table and the other one it's my data table. They have a one to many relation.

I want to get the median of a column, group by date (month date) like i have my other measures, but i cant get it to work.

The values that i want to get the median, are expresed in seconds.

So, what i want should be something like this:

And it should be able to filter by Consulta1[CANAL]

I tried creating a measure in many ways,

median (time_total) = MEDIAN(tablaPortabilidades[TPO_TOTAL])

median (time_total) = MEDIANX(tablaPortabilidades, tablaPortabilidades[TPO_ACEPT])

I also tried creating a measure that sums [TPO_ACEPT] and then creating a median of this sum, but same result.

The result:

Can someone give me a hand?

I don't know how to upload my .pbix here so i upload it to my drive.

.pbix

Here the data was imported, but im usind a direct query connection.

Pd: In the pbix the Consulta1 is the same as tablaPortabilidades

Thanks!

Re: Calculating median of time by group

bcdobbs — Fri, 10 Dec 2021 15:47:54 GMT

Hi,
Are you creating it as a measure? If so your dax is ok and seems to work with the demo you sent:

Here the measure has just been dropped into the values box of the matrix.

The only thing I can think is you might be trying to create a calculated column? If so MEDIAN isn't supported over direct query MEDIAN function (DAX) - DAX | Microsoft Docs

Re: Calculating median of time by group

Anonymous — Fri, 10 Dec 2021 16:02:53 GMT

I supposed it was something related with direct query. Is there a way to something like this with direct query? I can't import the whole data set because it's huge.

I tried doing it on sql but then i can't filter it.

Re: Calculating median of time by group

bcdobbs — Fri, 10 Dec 2021 16:23:43 GMT

My understanding is it should work if you just use it in a measure? Have you tried that?

Re: Calculating median of time by group

Anonymous — Fri, 10 Dec 2021 17:31:53 GMT

Yes, it actually works but only if a import the data. My issue is that i have to work with direct query, yes or yes. So, im looking for a way of doing the same but with direct q.

Re: Calculating median of time by group

bcdobbs — Fri, 10 Dec 2021 18:11:14 GMT

Can you expand the error details on the visual and send it over?

I've just setup an example using direct query to contoso in SQL and can make a MEDIAN measure work so wondering if there is another issue.