MIN with more than 2 values

[KarlinOz]

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.

[Thejeswar · Accepted solution]

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