Forum Discussion
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
- Thejeswar
Super User
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