Forum Discussion

KarlinOz's avatar
KarlinOz
Icon for Advocate III rankAdvocate III
8 years ago
Solved

MIN with more than 2 values

This has probably been covered elsewhere but after an hour of searching I'm going to post my own question. The nearest I ahve found to my question is MIN with Multiple Columns in DAX is that the solution I'll need to use? Here is my query.

 

I am creating a calculated column, example source data:

 

Tracking_category_1_option

1850 - Hutchinson - Fulham 

1860 - Wiley - Westend Pushing
x1625 - Kingbridge - Likely Rd
1720 – Sondal – Carlson Road (68m)
1838-Total Solutons Club-Punting

 

In my calculated column I'm extracting the number at the beginning of the string so the result should be:

 

 

TrackingCategory1_Option                 JobCode

1850 - Hutchinson - Fulham                     1850 

1860 - Wiley - Westend Pushing               1860
x1625 - Kingbridge - Likely Rd                   1625
1720 – Sondal – Carlson Road (68m)         1720
1838-Total Solutons Club-Punting             1838

 

What I thought I could do is:

JobCode =
VAR JobCodeBoundary1 = SEARCH("? -",Invoices[TrackingCategory1_Option],,20)
VAR JobCodeBoundary2 = SEARCH("?-",Invoices[TrackingCategory1_Option],,20)
VAR JobCodeBoundary3 = SEARCH("? –",Invoices[TrackingCategory1_Option],,20)
VAR JobCodeBoundary = MIN(INJobCodeBoundary1,JobCodeBoundary2,JobCodeBoundary3)
RETURN
if(LEFT(Invoices[TrackingCategory1_Option],1)="x", MID(Invoices[TrackingCategory1_Option],2,4), LEFT(Invoices[TrackingCategory1_Option],JobCodeBoundary))

 

But I found I can only use 2 arguments with the Min Function. Perhaps the best solution would be to create a temporary table with the 3 var values and use Min on that but I'm not sure how to acheieve that. Or is the best solution something like?:

 

JobCode = 
VAR JobCodeBoundary1 = SEARCH("? -",Invoices[TrackingCategory1_Option],,20)
VAR JobCodeBoundary2 = SEARCH("?-",Invoices[TrackingCategory1_Option],,20)
VAR JobCodeBoundary3 = SEARCH("? –",Invoices[TrackingCategory1_Option],,20)
VAR JobCodeBoundaryWkg = MIN(JobCodeBoundary1,JobCodeBoundary2)

VAR JobCodeBoundary = MIN(JobCodeBoundaryWkg ,JobCodeBoundary3)
RETURN 
if(LEFT(Invoices[TrackingCategory1_Option],1)="x", MID(Invoices[TrackingCategory1_Option],2,4), LEFT(Invoices[TrackingCategory1_Option],JobCodeBoundary))

 

It doesn't seem very elegant but it works.

 

 

  • Hi,

    You can use the below DAX

     

    JobCode = 
    VAR JobCodeBoundary1 = SEARCH("? -",Invoices[Tracking_category_1_option],,20)
    VAR JobCodeBoundary2 = SEARCH("?-",Invoices[Tracking_category_1_option],,20)
    VAR JobCodeBoundary3 = SEARCH("? –",Invoices[Tracking_category_1_option],,20)
    VAR JobCodeBoundary = MIN(JobCodeBoundary1,MIN(JobCodeBoundary2,JobCodeBoundary3))
    RETURN
    if(LEFT(Invoices[Tracking_category_1_option],1)="x", MID(Invoices[Tracking_category_1_option],2,4), LEFT(Invoices[Tracking_category_1_option],JobCodeBoundary))
    
     

    The Only difference is with the way VAR JobCodeBoundary is written. Rest all unchanged

1 Reply

  • Hi,

    You can use the below DAX

     

    JobCode = 
    VAR JobCodeBoundary1 = SEARCH("? -",Invoices[Tracking_category_1_option],,20)
    VAR JobCodeBoundary2 = SEARCH("?-",Invoices[Tracking_category_1_option],,20)
    VAR JobCodeBoundary3 = SEARCH("? –",Invoices[Tracking_category_1_option],,20)
    VAR JobCodeBoundary = MIN(JobCodeBoundary1,MIN(JobCodeBoundary2,JobCodeBoundary3))
    RETURN
    if(LEFT(Invoices[Tracking_category_1_option],1)="x", MID(Invoices[Tracking_category_1_option],2,4), LEFT(Invoices[Tracking_category_1_option],JobCodeBoundary))
    
     

    The Only difference is with the way VAR JobCodeBoundary is written. Rest all unchanged