cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johnlogistic Resolver I
Resolver I

Supposedly simple calculated column to get value of one rows across all rows per number.

Hey peoples,

I have this sample set of data

SEQORDERACTIVITYCOUNTRY CODEAMOUNT
100010ATTACHBE 
100020LOADINGBE5
100030LOADINGBE5
100040CR LOADINGBE2
100050CR LOADINGBE2
100060UNLOADINGRO10
100070CR UNLOADINGRO4
100080DETACHBE 
200010ATTACHFR 
200020LOADINGFR2
200030LOADINGFR2
200040UNLOADINGCH4
200050DETACHBE 

 

This is what I would like to get using a calculated column or measure

SEQORDERACTIVITYCOUNTRY CODEAMOUNTUL COUNTRY CODE
100010ATTACHBE RO
100020LOADINGBE5RO
100030LOADINGBE5RO
100040CR LOADINGBE2RO
100050CR LOADINGBE2RO
100060UNLOADINGRO10RO
100070CR UNLOADINGRO4RO
100080DETACHBE RO
200010ATTACHFR CH
200020LOADINGFR2CH
200030LOADINGFR2CH
200040UNLOADINGCH4CH
200050DETACHBE CH

So basically I want to get the CR UNLOADING OR UNLOADING country per seq. So that in my result i can display the destination country. You can assume that CR UNLOADING and UNLOADING are always the same country per seq.

So in my table i want to look like this

SEQ   |      Calculation     |   Destination

1000          10                        RO

2000           4                         CH

 

The calculation is a measure but is not important for this calculated column I think.

 

Any help is appreciated!

 

4 REPLIES 4
sduffy Helper I
Helper I

Re: Supposedly simple calculated column to get value of one rows across all rows per number.

Hi @Johnlogistic 

 

Would this work in your calculated column:

 

UL COUNTRY CODE= SWITCH(Table1[SEQ],1000, "RO", 2000, "CH")

 

 

Regards, Sduffy

 

Johnlogistic Resolver I
Resolver I

Re: Supposedly simple calculated column to get value of one rows across all rows per number.

HI @sduffy 

No this will not work since this is sample data I t has to be dynamic as the real data set has hundreds of different SEQ with each their own country codes which are a wide range of countries and combination of cr loading, unloading etc. 

EricHulshof Solution Supplier
Solution Supplier

Re: Supposedly simple calculated column to get value of one rows across all rows per number.

Hello,

Assuming there is a relationship between the 2 tables you can use the calculated column:

UL COUNTRY CODE = RELATED(Table2[DESTINATION])

 

Johnlogistic Resolver I
Resolver I

Re: Supposedly simple calculated column to get value of one rows across all rows per number.

hi @EricHulshof 

There are no 2 tables Its one table. The way to look at it is SEQ is a trip. A trip has loading and unloading (and/or cr loading/unloading) activities. Now in my calculation I do something with the loaded amount but per SEQ or trip i want the destination country so the country code of the UNLOADING activity of that SEQ again it can be CR UNLOADING or UNLOADING

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors