topic Re: MEDIANX returns blank, MAXX and MINX work in DAX Commands and Tips

MEDIANX returns blank, MAXX and MINX work

hyndmanevanp — Wed, 13 Dec 2023 16:26:26 GMT

I'm linked to our companies QuickBooks Online data. I'm trying to display the Max, Median, and Min for client hours. We have four regions and four client types. I'm using the Items table to separate client types. I'm using the Multi-Row Card visual to display and using the visual filters to isolate one month and office. My measure is:

Hours_Private_Median = MEDIANX(DISTINCT(Invoice[CustomreRef_Name], CALCULATE(SUM(Invoice[Line_Detail_Qty]), 'Item'[Fully_Qualified_Name]="Private"))

When I use this is MAXX and MINX, it works, and I've cross referrenced it to a separate data set. I've replaced MEDIANX in the working MAXX measure incase I had an unseen entry error, but it returns blank when changed. Further, when I change regions, some client types return an incorrect number, whereas some client types always return blank MEDIANX. Images below.

Region 1 - All Median blank

Region 2 - Private return incorrect amount

Region 2 Cluster Column Chart with Median constant line on (48)

Thank you

Re: MEDIANX returns blank, MAXX and MINX work

WinterMist — Wed, 13 Dec 2023 17:38:03 GMT

hyndmanevanp

This is going to depend on your data.

Please check out this article.

https://exceleratorbi.com.au/medianx-returns-blank-in-dax-part-1/

Regards,

Nathan

Re: MEDIANX returns blank, MAXX and MINX work

hyndmanevanp — Wed, 13 Dec 2023 21:19:10 GMT

WinterMist

Thank you for the link above. The author talked about the handling of blanks in DAX vs visual. He solved it using something called DAX Studio, I'm not sure what that is. I went to my data and removed empty cells, if existent, from the Invoice ID and Line Qty columns, but it didn't change my results.

Re: MEDIANX returns blank, MAXX and MINX work

hyndmanevanp — Thu, 14 Dec 2023 13:43:16 GMT

WinterMist

One additional comment: I have a measure to capture the hours of each client type, that measure in inbedded in the one from my first post:

Hours_Private = CALCULATE(SUM(Invoice[Line_Detail_Qty]), 'Item[Fully_Qualified_Name]="Private"))

I can use the measure above and create another measure using regular MAX and MIN, but on only returns the max and min of each individual line item, not summed, however when I try MEDIAN, it won't even allow Hours_Private as an option. I'm adding this incase it can be used in another measure.