cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KarlinOz
Helper IV
Helper IV

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.

 

 

1 ACCEPTED SOLUTION
Thejeswar
Memorable Member
Memorable Member

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

View solution in original post

1 REPLY 1
Thejeswar
Memorable Member
Memorable Member

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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.