topic Re: Question about the MAXX in DAX Commands and Tips

Question about the MAXX

VSIM — Mon, 05 Oct 2020 18:57:59 GMT

Hello. I have similar table:
I want to find the max difference between rows in column "Numbers" and corresponding letter in "Letters" to show them in 2 KPI visuals in the reports (as a result, I mean to get "F" and 8, as the maximum difference from the numbers rows is between 20 and 12, and not 5-2, or 9-5, or 6-9, or 12-6....). Can anybody tell me how to write the measure for that? Thanks in advance

Re: Question about the MAXX

daxer-almighty — Mon, 05 Oct 2020 20:13:02 GMT

This is not clear. What happens when somebody filters the table by letters: A, D, H? What result do you expect?

Re: Question about the MAXX

VSIM — Tue, 06 Oct 2020 05:10:33 GMT

After filter A, D, H, the result will be "H" and 25-6 = "19", as 19 is more then (D-A ) 6-2="4". Actually, I have incremental date column instead of letters column in reality. I put the table with letters here for simpicity (with my opinion).

Re: Question about the MAXX

Anonymous — Tue, 06 Oct 2020 11:54:38 GMT

// Let's call the table T. [Max Diff Value] = // It does not matter whether // the Letters column stores // letters or numbers or dates // as long as the entries may // be sorted. var __lettersWithDiffs = GENERATE( T, var __currentLetter = T[Letter] var __currentNumber = T[Number] // can be BLANK if we're on the first // letter as seen in the current // context var __prevLetter = MAXX( filter( T, T[Letter] < __currentLetter ), T[Letter] ) // can be BLANK if we're on the first // letter as seen in the current // context var __prevNumber = MAXX( filter( T, T[Letter] = __prevLetter ), T[Number] ) var __diff = __currentNumber - __prevNumber RETURN row( "@PrevLetter", __prevLetter, "@Diff", __diff ) ) var __maxDiff = MAXX( __lettersWithDiffs, [@Diff] ) RETURN __maxDiff

and the first letter where the max diff is found:

[Max Diff Letter] = // It does not matter whether // the Letters column stores // letters or numbers or dates // as long as the entries may // be sorted. var __lettersWithDiffs = GENERATE( T, var __currentLetter = T[Letter] var __currentNumber = T[Number] // can be BLANK if we're on the first // letter as seen in the current // context var __prevLetter = MAXX( filter( T, T[Letter] < __currentLetter ), T[Letter] ) // can be BLANK if we're on the first // letter as seen in the current // context var __prevNumber = MAXX( filter( T, T[Letter] = __prevLetter ), T[Number] ) var __diff = __currentNumber - __prevNumber RETURN row( "@PrevLetter", __prevLetter, "@Diff", __diff ) ) var __maxDiffLetter = MAXX( TOPN(1, __lettersWithDiffs, // this sorts by the differences // in a descending order [@Diff], DESC, // and this sorts within the // same differences by letters // in an ascending order T[Letter], ASC ), T[Letter] ) RETURN __maxDiffLetter

Re: Question about the MAXX

Anonymous — Tue, 06 Oct 2020 12:05:25 GMT

Of course, the stuff above works correctly but you should understand that if you put the measures in a table and then drop the letters onto rows, they will be calculating values for THIS ROW ONLY. If you want to get these measures calculated on a selection of rows, then you have to put the measures onto a card or check the measures' values on the Total row in a table or in a matrix.