cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
hafizuddinb Frequent Visitor
Frequent Visitor

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

Community Support Team
Community Support Team

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 @hafizuddinb

 

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.
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.

6 REPLIES 6
hafizuddinb Frequent Visitor
Frequent Visitor

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 @hafizuddinb

 

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

hafizuddinb Frequent Visitor
Frequent Visitor

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!!!

Community Support Team
Community Support Team

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 @hafizuddinb

 

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.
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.