Forum Discussion
Helper IIIDax Help - Min and Max
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! -
| B.Expected results( At Order level) | ||
| Order# | No of Days | |
| ABC | -15 | |
| DEF | 0 | |
| XYZ | -10 | |
| PQR |
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:
6 Replies
- lbendlin
Super User
Slightly refactored:
Order level = var mxx = maxx('Table',[No of Days]) return if (mxx<0,minx('Table',[No of Days]),mxx) - 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.
- parry2k
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
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]))),
