Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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
Ashish_Mathur
Super User
Super User

Hi,

 

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

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

Hi,

 

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

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Hi Ashish_Mathur,

 

It works as I expected.

 

Zillion thanks!!!

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.
Anonymous
Not applicable

Sorry, Column A should start at Month

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
    )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.