max measure is showing infinity value

lawadaa — Thu, 05 Oct 2023 20:11:52 GMT

hello

i created 2 measures to find the max and min percentage of another measure. the dax code to create the 2 measures is the same the only difference is in the minimum measure i used (minx) and in the maximum measure i used (max). although the 2 measures are exactly the same , the max measure it showing me (infinity ) in case the percentage is greater than 100% and the minimum function is working properly. i cant understand why it's showing inifinty and not the correct percentage

this the code for the minumum percentage measure

Min New Achievement % overall = CALCULATE(

minX(SUMMARIZE('categories trans','categories trans'[AGENT_ID],'categories trans'[AGENT_NAME],'categories trans'[TRANSDATE (bins)],'categories trans'[base type without zeed],'categories trans'[categories summarize],'Agents list'[Position],'categories trans'[CHANNEL],'Agent Rate Key'[Agent Rate]),[achiev of overall]),ALL('Agents list'[Position]),ALL('categories trans'[categories summarize],'categories trans'[base type without zeed]),'categories trans'[achievement],ALL('Agent Rate Key'[Agent Rate]),'categories trans'[categories summarize]<>BLANK())

this is the code for the max percentage measure

Max New Achievement % overall =

CALCULATE(

maxX(SUMMARIZE('categories trans','categories trans'[AGENT_ID],'categories trans'[AGENT_NAME],'categories trans'[TRANSDATE (bins)],'categories trans'[base type without zeed],'categories trans'[categories summarize],'Agents list'[Position],'categories trans'[CHANNEL],'Agent Rate Key'[Agent Rate]),[achiev of overall]),ALL('Agents list'[Position]),ALL('categories trans'[categories summarize],'categories trans'[base type without zeed]),'categories trans'[achievement],ALL('Agent Rate Key'[Agent Rate]),'categories trans'[categories summarize]<>BLANK())

the is the [achiev of overall] measure

achiev of overall = [core achievement]+[access achiev 10%]

this is the result

max measure should show the value 149.82% instead of infinity

note that i had to use summarize function as i want to show the min/max achievement percentage per agent, category, category type, and position and then find the maximum and minimum value percentage among all categories.

Re: max measure is showing infinity value

ValtteriN — Fri, 06 Oct 2023 05:52:27 GMT

Hi,

First, here are some general comments:

1. I recommend using variables for the table you want to use MAXX/MINX for.
2. Use MAXX/MINX as the overall measure, not within CALCULATE
3. With the variable approach you can easily create a calculated table to view if your logic is as expected
With these in mind try something like this:

Example data:

Dax:

MAXX achivement =

var _vtable = SUMMARIZE('Table','Table'[Category],"achievement measure" , [Achievement overall]) return

MAXX(_vtable,[achievement measure])

Calculated table test:

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

topic max measure is showing infinity value in DAX Commands and Tips

max measure is showing infinity value

Re: max measure is showing infinity value