Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
A | B | C | D | E | F | G | H |
1 | Month | Vehicle ID | Location | Min Month | First Location | Max Month | Final Location |
2 | Apr-18 | ABC123 | 2GS AAAAA | 4/1 | 2GS AAAAA | 12/1 | 3PPJ2 EEEEE |
3 | May-18 | ABC123 | 5PPJ BBBBB | 4/1 | 2GS AAAAA | 12/1 | 3PPJ2 EEEEE |
4 | Jun-18 | ABC123 | 1PKJ CCCCC | 4/1 | 2GS AAAAA | 12/1 | 3PPJ2 EEEEE |
5 | Jul-18 | ABC123 | 1PKJ CCCCC | 4/1 | 2GS AAAAA | 12/1 | 3PPJ2 EEEEE |
6 | Aug-18 | ABC123 | 1PKJ CCCCC | 4/1 | 2GS AAAAA | 12/1 | 3PPJ2 EEEEE |
7 | Sep-18 | ABC123 | 1PKJ CCCCC | 4/1 | 2GS AAAAA | 12/1 | 3PPJ2 EEEEE |
8 | Oct-18 | ABC123 | 4PKJ2 DDDDD | 4/1 | 2GS AAAAA | 12/1 | 3PPJ2 EEEEE |
9 | Nov-18 | ABC123 | 4PKJ2 DDDDD | 4/1 | 2GS AAAAA | 12/1 | 3PPJ2 EEEEE |
10 | Dec-18 | ABC123 | 3PPJ2 EEEEE | 4/1 | 2GS AAAAA | 12/1 | 3PPJ2 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!
Solved! Go to Solution.
Hi,
Is this the result you are expecting? You may download my PBI file from here.
Hope this helps.
Hi @Anonymous
Glad to hear you've solved it, please accept the helpful reply as solution to close this thread.
Regards,
Cherie
You are welcome. If my reply helped, please mark it as Answer.
Hi,
Is this the result you are expecting? You may download my PBI file from here.
Hope this helps.
Hi Ashish_Mathur,
It works as I expected.
Zillion thanks!!!
You are welcome. If my reply helped, please mark it as Answer.
Hi @Anonymous
Glad to hear you've solved it, please accept the helpful reply as solution to close this thread.
Regards,
Cherie
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 )
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |