topic Re: Dax for Min and Max in DAX Commands and Tips

Dax for Min and Max

RRaj_293 — Thu, 07 Sep 2023 11:28:50 GMT

Hi ,

Scenario- I have a Line Table (A in below pic) which has different orders and their lines for which certain number of days field is calculated. It has negative and positive numbers. The various possibilities are as shown below.

A.Line Table
Order#	Line#	No of Days
ABC	1	-4
ABC	2	-8
ABC	3	-15
DEF	1	0
XYZ	1	3
XYZ	2	5
XYZ	3	10
PQR	1	-8
PQR	2	5
PQR	3	17

The above table which is at Line level is aggregated to Order level and another table exists in the Model which has all measures only at the Order level. In this Existing Order table I need to write a dax to get the below desired result of No of days -

Tried this dax - Doesnt seem to work! -

NoOfDays_New =

Var nodays = SELECTEDVALUE('Line Table'[NoOfDays])

Return

SWITCH(TRUE(),

nodays < 0, MAX(-1* MIN('Line Table'[NoOfDays])),

nodays > 0, MAX('Line Table'[NoOfDays]),

nodays = 0 , "On Time" )

B.Expected results( At Order level)
Order#	No of Days
ABC	-15
DEF	0
XYZ	10
PQR	17

Re: Dax for Min and Max

eliasayyy — Thu, 07 Sep 2023 11:57:36 GMT

hello RRaj_293

MAX = VAR _ml = CALCULATE(MAX('Table'[Line]),ALLEXCEPT('Table','Table'[Order])) VAR _c = CALCULATE( MAX('Table'[No Days]) , 'Table'[Line] = _ml) RETURN _c

result

Re: Dax for Min and Max

RRaj_293 — Fri, 08 Sep 2023 03:46:45 GMT

Appreciate you taking time to respond .

If I understand your dax correclty , You are taking the maximum line numer from line table and then filtering the order table to equate that value. But my bad the examples I put all have the max number (desired output) in the max line of an order , but thats just a coincidence , we could have the max/min value even at line 1 out of 10 lines , so its random and no sequence to it.

The dax will go into the Order table which is aggregated form of Line table , So the Order table will not have line numbers(As shown exactly in my desired results). It will have the No of Days calcualted based on Max/Min only at Order level, Dax doesnt have to return the line number.

Also there is one more scenario I missed quoting- We also have blanks ,If Any line has a blank then Order table will have a blank against that order like in Ex- PQR

A.Line Table
Order#	Line#	No of Days
ABC	1	-4
ABC	2	-20
ABC	3	-15
DEF	1	0
XYZ	1	18
XYZ	2	5
XYZ	3	10
PQR	1	-8
PQR	2
PQR	3	5
PQR	4	17


B.Expected results( At Order level)	Order Table(DAX Calculated - Aggregation of Line Table)
Order#	No of Days
ABC	-20
DEF	0
XYZ	18
PQR

This seem to work for all scenarios except blanks. Using ISBlank is throwing error on data types.

NoOfDays_Calc = if((SELECTEDVALUE('Line'[NoOfDays]))<=0, Min(' Line'[NoOfDays]), MAX('Line'[NoOfDays]))

Thanks.

Re: Dax for Min and Max

eliasayyy — Thu, 07 Sep 2023 12:43:14 GMT

RRaj_293 i do not follow after the blank part in your expected result for abc , you put no of days is -20 however the last line is -15 and for xyz is 10 so can you please explain

Re: Dax for Min and Max

eliasayyy — Thu, 07 Sep 2023 12:56:09 GMT

try this RRaj_293

MAX = VAR _c = CALCULATE(COUNTBLANK('Table (2)'[No of Days]), ALLEXCEPT('Table (2)', 'Table (2)'[Order#])) VAR _m = CALCULATE(MAXX('Table (2)',ABS('Table (2)'[No of Days])),ALLEXCEPT('Table (2)','Table (2)'[Order#])) VAR _mt = MAXX(FILTER('Table (2)' , ABS('Table (2)'[No of Days]) = _m),'Table (2)'[No of Days]) RETUrn IF(_c > 0 , BLANK() , _mt)

Re: Dax for Min and Max

RRaj_293 — Fri, 08 Sep 2023 03:45:23 GMT

Hi ,

Let me give you some context of what Im trying to do. Basically one order can have multiple lines. So within that order (In the Line Table - Table A) we have already calcualted the number of days between two dates , that is the field "NoOfDays" in Line table.

Now we have another table , B which is at the order level only(no line deatils) which already has some other measures aggregated. To this table now , I have to do similar aggregation on the field NoofDays from Line table and bring it as a new column where it will show only the worst case scenario at the order level.

so If one order example below has No of days as -ve , +ve and blank , we need Blank to show at Order level as blank indicates one of the dates used for calculating NoofDays was a blank, Hence pick blank.

PQR	1	-8
PQR	2
PQR	3	5
PQR	4	17

So in above order - PQR in the order table (B) should show me blank against order# PQR .

In the below scenario of Order ABC where all lines have -ve number , we need to take the farthest days which is -20 .

ABC	1	-4
ABC	2	-20
ABC	3	-15

Hence ,

All + days - Max(NoofDays)

All -ve = Min(NoofDays) as thats the farthest gap of days. Worst case scenario.

+ve,-ve,blanks = Blanks as that indicates missing date in the No of days calculation which is the worst case.

Hope this clarifies.

Also tried your dax it gave me the error -

Even just this line -

VAR _c = CALCULATE(COUNTBLANK('Table (2)'[No of Days]), ALLEXCEPT('Table (2)', 'Table (2)'[Order#]))

brings same error.

Note - The Order table is not a physical table in Database, Its a DAX calcualtion in the below format,

Order = summarize('Line',

'Line'[Order#],

"Startdate", Min('Line[start date]),

.......

"NoOfDays", --*** Field we are trying to calculate for all above scenarios--

if((SELECTEDVALUE('Line'[NoOfDays]))<=0, Min('Line'[NoOfDays]), MAX('Line',[NoOfDays]))---***not working LIne[No Of days] = Blank*****

)

Re: Dax for Min and Max

RRaj_293 — Fri, 08 Sep 2023 01:04:36 GMT

sorry if that created a confusion , I basically changed the table values to show like in my first post the max/min values will not necessarily be on the max(line#) within an order , like you assumed in the first reply. so to avoid that confusion I changed those values. We can stick to the second set of tables.

Re: Dax for Min and Max

eliasayyy — Fri, 08 Sep 2023 06:29:44 GMT

hello RRaj_293 change Table (2) with the name of your table

Re: Dax for Min and Max

RRaj_293 — Fri, 08 Sep 2023 06:35:29 GMT

Yes , thats what I did(changed to Line table name) and got this error! I dont think it should be Order table as there is no field 'NoofDays' yet in the Order table and that the field we are trying to calculate at Order level.

Re: Dax for Min and Max

eliasayyy — Fri, 08 Sep 2023 06:37:38 GMT

hmm i see can yo ushow me what tables you have and what they contain to better understand?

Re: Dax for Min and Max

RRaj_293 — Fri, 08 Sep 2023 06:47:49 GMT

Line table to the right is the table A in my pic which has Orders at Line level(lower granularity). The Header table is the summarized(using dax query) table(table -B Order table) which is aggregate version of the line table only at the order level.

Re: Dax for Min and Max

RRaj_293 — Fri, 08 Sep 2023 07:07:43 GMT

Line table to the right is the table at lower granularity (Order#(1)-(M)LIne#) , which is the table A in my post. Header table to the left is the Order table(Table-B - Order in my post) which is the Aggregated version of line (aggregated using dax summarize query) table.

Re: Dax for Min and Max

eliasayyy — Fri, 08 Sep 2023 07:26:21 GMT

is it ok if you share a sample of your powerbi ? like create ne wone with insenstive information so i can understand better

Re: Dax for Min and Max

RRaj_293 — Fri, 08 Sep 2023 08:54:10 GMT

Ok , Below is the screenshot of the tables. At the Order Header the calcualtion I have included is the one I created which does not show blanks .

NoofDays is basically the difference in days between despatch date and Target depsatch date. If the despacth date is after the target date then its a negative difference in days , on same day as target date then 0 , if before +ve number . If despatch date is blank then Blank.

The blank scenario is what is not coming working. All different scenarios explained in previous replies have been covered in sample data here.

File Sent.

Re: Dax for Min and Max

RRaj_293 — Fri, 08 Sep 2023 08:41:29 GMT

I'm unable to find an option to attach Pbix , not sure if it needs spl privileges!