Dax Help - Min and Max

[RRaj_293]

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
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

[RRaj_293]

Thank you all for responding. I was able to resolve it using below dax-

IF ( (COUNTBLANK( 'Line'[NoOfDays] )>=1 ),BLANK(),IF((SELECTEDVALUE('Line'[NoOfDays]))<=0, MIN('Line',' Line'[NoOfDays]), MAX('Line','Line'[NoOfDays]))),

[parry2k]

@RRaj_293 Cannot tell, I tested on the sample you provided and it worked. You cannot compare with 0 because blank is deemed as zero and that's why I had that special logic in place to count the blank rows.

[parry2k · Accepted solution]

@RRaj_293 try this measure: but you have to turn on show items with no data because when you are returning BLANK () where the

No of Days Measure = 
VAR __MaxDays = MAXX ( Orders, [No of Days] )
VAR __MinDays = MINX ( Orders, [No of Days] )
VAR __CountBlank = COUNTROWS ( FILTER ( Orders, ISBLANK ( [No of Days] ) ) )
RETURN
IF ( __CountBlank >= 1, BLANK (), IF ( __MinDays < 0, __MinDays, __MaxDays ) ) 

Tweak the above measure as you see fit, as I didn't understand the full logic.

order has a blank number of days by default any visual will suppress that rows:

[RRaj_293]

Thanks @parry2k  . I tried this and logically looks right to me as well but not sure why the records are all blank. I have enabled 'show items with No data'.  If I remove the blank count and just do Min , Max it works except for blank scenarios.

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

[RRaj_293]

Hi @lbendlin  , Thanks for responding. Yes I got the same using If condition , but the challenge is with the blanks. I have modified the data in the table and the expected reuslts. If there is a combination of +ve , -ve and blank within an order then I need blank displayed at the order level as we are reporting the worst case scanerios . A blank indicates the order is not yet despatched likein order example PQR. If the order has only +ve and -ve's then display the min(farthest) negative number as that indiactes the most number of days taken(worst case scenario) like in example order XYZ.

Note - Table data edited for these scenarios in my original post.

[lbendlin]

Slightly refactored:

Order level = 
var mxx = maxx('Table',[No of Days])
return if (mxx<0,minx('Table',[No of Days]),mxx)