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

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

Hi @Anonymous 

 

Would this work in your calculated column:

 

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

 

 

Regards, Sduffy

 

Anonymous
Not applicable

HI @Anonymous 

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. 

Hello,

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

UL COUNTRY CODE = RELATED(Table2[DESTINATION])

 


Quality over Quantity


Did I answer your question? Mark my post as a solution!


Anonymous
Not applicable

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