Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
agustiny
Frequent Visitor

Calculating median of time by group

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.

 

agustiny_0-1639147276473.png

 

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:

agustiny_0-1639149054638.png

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:

agustiny_1-1639147847010.png

 

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!

 

1 ACCEPTED SOLUTION
bcdobbs
Community Champion
Community Champion

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

bcdobbs_0-1639151183601.png

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



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

5 REPLIES 5
bcdobbs
Community Champion
Community Champion

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

bcdobbs_0-1639151183601.png

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



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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.

bcdobbs
Community Champion
Community Champion

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



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

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.

bcdobbs
Community Champion
Community Champion

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.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.