Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hey peoples,
I have this sample set of data
SEQ | ORDER | ACTIVITY | COUNTRY CODE | AMOUNT |
1000 | 10 | ATTACH | BE | |
1000 | 20 | LOADING | BE | 5 |
1000 | 30 | LOADING | BE | 5 |
1000 | 40 | CR LOADING | BE | 2 |
1000 | 50 | CR LOADING | BE | 2 |
1000 | 60 | UNLOADING | RO | 10 |
1000 | 70 | CR UNLOADING | RO | 4 |
1000 | 80 | DETACH | BE | |
2000 | 10 | ATTACH | FR | |
2000 | 20 | LOADING | FR | 2 |
2000 | 30 | LOADING | FR | 2 |
2000 | 40 | UNLOADING | CH | 4 |
2000 | 50 | DETACH | BE |
This is what I would like to get using a calculated column or measure
SEQ | ORDER | ACTIVITY | COUNTRY CODE | AMOUNT | UL COUNTRY CODE |
1000 | 10 | ATTACH | BE | RO | |
1000 | 20 | LOADING | BE | 5 | RO |
1000 | 30 | LOADING | BE | 5 | RO |
1000 | 40 | CR LOADING | BE | 2 | RO |
1000 | 50 | CR LOADING | BE | 2 | RO |
1000 | 60 | UNLOADING | RO | 10 | RO |
1000 | 70 | CR UNLOADING | RO | 4 | RO |
1000 | 80 | DETACH | BE | RO | |
2000 | 10 | ATTACH | FR | CH | |
2000 | 20 | LOADING | FR | 2 | CH |
2000 | 30 | LOADING | FR | 2 | CH |
2000 | 40 | UNLOADING | CH | 4 | CH |
2000 | 50 | DETACH | BE | 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!
Hi @Anonymous
Would this work in your calculated column:
Regards, Sduffy
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |