cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

MIN(IF(B:B=B10,A:A)) , INDEX(C:C,MATCH(1,INDEX((B:B=B2)*(A:A=D2),0),0)) and MAX

Hello,

 

I am trying to add 4 additional column on my table below. I have it perfectly done in excel but when i tried in DAX it doesn't giving the result that i wanted. The idea is to have a 'Month' as slicer in Power BI to control from any selected start and end 'Month'. My table will continously append as month goes by. The outcome should be able to tell me where is my earliest and final location of my vehicle being deployed. 

 

ABCDEGH
1MonthVehicle IDLocationMin MonthFirst LocationMax MonthFinal Location
2Apr-18ABC1232GS AAAAA4/12GS AAAAA12/13PPJ2 EEEEE
3May-18ABC1235PPJ BBBBB4/12GS AAAAA12/13PPJ2 EEEEE
4Jun-18ABC1231PKJ CCCCC4/12GS AAAAA12/13PPJ2 EEEEE
5Jul-18ABC1231PKJ CCCCC4/12GS AAAAA12/13PPJ2 EEEEE
6Aug-18ABC1231PKJ CCCCC4/12GS AAAAA12/13PPJ2 EEEEE
7Sep-18ABC1231PKJ CCCCC4/12GS AAAAA12/13PPJ2 EEEEE
8Oct-18ABC1234PKJ2 DDDDD4/12GS AAAAA12/13PPJ2 EEEEE
9Nov-18ABC1234PKJ2 DDDDD4/12GS AAAAA12/13PPJ2 EEEEE
10Dec-18ABC1233PPJ2 EEEEE4/12GS AAAAA12/13PPJ2 EEEEE

 

D2=MIN(IF(B:B=B2,A:A))
E2=INDEX(C:C,MATCH(1,INDEX((B:B=B2)*(A:A=D2),0),0))
F2=MAX(IF(B:B=B2,A:A))
G2=INDEX(C:C,MATCH(1,INDEX((B:B=B2)*(A:A=F2),0),0))

 

I have about 10k of vehicle ID records for every 'Month' and to prepare the database in excel using above formula is too much to process. The rule of thumb is all 4 column I have highlighted above should automaticly display the earliest and final location depanding on my 'Month' slicer and also not just sort first/last according to 'Location' alphabetical order

 

Thank you!

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: MIN(IF(B:B=B10,A:A)) , INDEX(C:C,MATCH(1,INDEX((B:B=B2)*(A:A=D2),0),0)) and MAX

Hi,

 

Is this the result you are expecting?  You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

View solution in original post

v-cherch-msft Super Contributor
Super Contributor

Re: MIN(IF(B:B=B10,A:A)) , INDEX(C:C,MATCH(1,INDEX((B:B=B2)*(A:A=D2),0),0)) and MAX

Hi @Anonymous

 

Glad to hear you've solved it, please accept the helpful reply as solution to close this thread.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User
Super User

Re: MIN(IF(B:B=B10,A:A)) , INDEX(C:C,MATCH(1,INDEX((B:B=B2)*(A:A=D2),0),0)) and MAX

You are welcome.  If my reply helped, please mark it as Answer.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Re: MIN(IF(B:B=B2,A:A)) , INDEX(C:C,MATCH(1,INDEX((B:B=B2)*(A:A=D2),0),0)) and MAX

Sorry, Column A should start at Month
Super User
Super User

Re: MIN(IF(B:B=B2,A:A)) , INDEX(C:C,MATCH(1,INDEX((B:B=B2)*(A:A=D2),0),0)) and MAX

Hi @Anonymous

 

It might be more convenient to do this with measures but let's try this for your columns:

Min Month =
CALCULATE ( MIN ( Table1[Month] ), ALLEXCEPT ( Table1, Table1[Vehicle ID] ) )

 

First Location =
VAR _MinMonth =
    CALCULATE ( MIN ( Table1[Month] ), ALLEXCEPT ( Table1, Table1[Vehicle ID] ) )
RETURN
    CALCULATE (
        VALUES ( Table1[Location] ),
        ALLEXCEPT ( Table1, Table1[Vehicle ID] ),
        Table1[Month] = _MinMonth
    )

 

Max Month  = CALCULATE(MAX(Table1[Month]), ALLEXCEPT(Table1, Table1[Vehicle ID]))

 

Final Location =
VAR _MaxMonth =
    CALCULATE ( MAX ( Table1[Month] ), ALLEXCEPT ( Table1, Table1[Vehicle ID] ) )
RETURN
    CALCULATE (
        VALUES ( Table1[Location] ),
        ALLEXCEPT ( Table1, Table1[Vehicle ID] ),
        Table1[Month] = _MaxMonth
    )
Super User
Super User

Re: MIN(IF(B:B=B10,A:A)) , INDEX(C:C,MATCH(1,INDEX((B:B=B2)*(A:A=D2),0),0)) and MAX

Hi,

 

Is this the result you are expecting?  You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png

View solution in original post

Anonymous
Not applicable

Re: MIN(IF(B:B=B10,A:A)) , INDEX(C:C,MATCH(1,INDEX((B:B=B2)*(A:A=D2),0),0)) and MAX

Hi Ashish_Mathur,

 

It works as I expected.

 

Zillion thanks!!!

v-cherch-msft Super Contributor
Super Contributor

Re: MIN(IF(B:B=B10,A:A)) , INDEX(C:C,MATCH(1,INDEX((B:B=B2)*(A:A=D2),0),0)) and MAX

Hi @Anonymous

 

Glad to hear you've solved it, please accept the helpful reply as solution to close this thread.

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Super User
Super User

Re: MIN(IF(B:B=B10,A:A)) , INDEX(C:C,MATCH(1,INDEX((B:B=B2)*(A:A=D2),0),0)) and MAX

You are welcome.  If my reply helped, please mark it as Answer.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 51 members 1,381 guests
Please welcome our newest community members: