topic Re: Ignore Dimension to give median total in DAX Commands and Tips

Ignore Dimension to give median total

jasemilly — Mon, 18 Aug 2025 15:16:17 GMT

I have a simple fact table to show when someone has created a job.

It has Date, Persons, Name, and a key field.

I would like to show the Total Number of jobs a person has created and a median measure for all Persons so can see how that person comprare to the median, I created this measure and using the persons name from the fact table AgentCreated and it works perfectly giving the median value.

VAR PersonTotals = SUMMARIZE ( ALL ( AgentCreated[CreatedBy] ), -- all persons in current date range AgentCreated[CreatedBy], "TotalPerPerson", countrows(AgentCreated) ) -- total qty for that person RETURN MEDIANX ( PersonTotals, [TotalPerPerson] )

I have a DimAgents table that has a relationship with the FactagentCreated using the CreatedBy field and a stand date dimension table relationship on the CreatedDate.

Now when I put a slicer on the page using the Name from the DimAgents table and amend my measure to this. the measure doesn't return anything.

SUMMARIZE ( ALL ( DimAgents[Full Name] ), // ignore agent slicer DimAgents[Full Name], "TotalPerPerson", CALCULATE ( COUNTROWS ( AgentCreated ), KEEPFILTERS ( VALUES ( 'Date'[Date] ) ) // keep date slicer filter ) ) RETURN MEDIANX ( PersonTotals, [TotalPerPerson] )

I don't know what part I am doing wrong.

Thanks for any help

Re: Ignore Dimension to give median total

Greg_Deckler — Mon, 18 Aug 2025 15:51:48 GMT

jasemilly Can you post sample data?

Re: Ignore Dimension to give median total

jasemilly — Mon, 18 Aug 2025 16:31:58 GMT

here is how the agentcreated looks

CreatedDate, CreatedDateTime,CreatedBy, Key, JobentryTypedId, ShiftType
28/09/2024, 28/09/2024 07:01 , Jason, 500, 1 , 0

28/09/2024, 28/09/2024 08:01, Jason, 550,1,0

28/09/2024, 28/09/2024 11:00, Jason, 560,1,0

28/09/2024, 28/09/2024 08:01, Ian, 505,1,0
28/09/2024, 28/09/2024 08:10, Ian, 506,1,0

28/09/2024, 28/09/2024 08:15, Ian, 600,1,0

28/09/2024, 28/09/2024 08:20, Ian, 605,1,0
28/09/2024, 28/09/2024 08:21, Fred, 705,1,0

28/09/2024, 28/09/2024 08:30, Fred, 707,1,0

DimAgent would be

Name, FullName

Fred, Fred Smith

Ian, Ian Rush

Jason, Jason Walker

Re: Ignore Dimension to give median total

FBergamaschi — Mon, 18 Aug 2025 17:33:28 GMT

Hello

I replicated the scenario and it works, there were some issues in the names.

This is the result I have

My pbix is here, based on your data

https://drive.google.com/drive/folders/1qgE_Uqk7LRJzRf_WXHl-0W91Guwe21M6?usp=sharing

If this helped, please consider giving kudos and mark as a solution

me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

Re: Ignore Dimension to give median total

jasemilly — Tue, 19 Aug 2025 10:01:06 GMT

Thanks for this, works great in test. However I am getting a blank on my actual report and I was thinking that it's because I had lots of agents that hadn't created anything, so I altered my PQ to filter for 2 agents that had created and it still shows 0 for the median on my report.

maybe I need to filter the dimension table to the same 2 people as well.

Re: Ignore Dimension to give median total

v-venuppu — Wed, 20 Aug 2025 12:51:52 GMT

Hi jasemilly ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you FBergamaschi Greg_Deckler for the prompt response.

Yes, filtering the DimAgents table to only include the 2 agents who have created jobs is the right approach. Apply a slicer on DimAgents[FullName] to select those 2 agents, and your DAX measure will then calculate the median based only on their job counts. This will prevent blanks or zeros from agents who haven’t created jobs.

Thank you.

Re: Ignore Dimension to give median total

v-venuppu — Mon, 25 Aug 2025 07:15:15 GMT

Hi jasemilly ,

I wanted to check if you had the opportunity to review the information provided and resolve the issue..?Please let us know if you need any further assistance.We are happy to help.

Thank you.