Forum Discussion
Multi-Conditional Lookups
Below is a simplified example of a very large data set I am working with. The goal is to consolidate data based on the tracking number.
I have two tables, one with a large amount of raw data, showing me every transaction related to a specific tracking number, and I created a table that summarizes the tracking numbers where I will be doing all my calculations. There are two issues I am running into with calculating the data.
1. Zone
I cannot LOOKUP the zone since at times the data changes. I need to ask for the most recent, non-blank transaction for zone.
2. Weight
I cannot LOOKUP or SUM weight since most often a change in weight is a correction. I need to ask for the most recent, non-blank, non-zero transaction for weight.
| Tracking Number | date | Weight | Zone |
| 1001 | 1/1/2019 | 1 | 250 |
| 1001 | 1/2/2019 | 101 | |
| 1002 | 1/3/2019 | 5 | 101 |
| 1002 | 1/4/2019 | 6 | 250 |
| 1002 | 1/5/2019 | 0 |
Thoughts?
10 Replies
Since you have robust line-by-line data, you might be able to try a measures like this:Zone= CALCULATE('Table'[Zone], FILTER(ALL([Transaction Date]) , MAX([Transaction Date])))Weight= CALCULATE('Table'[Weight], FILTER(ALL([Transaction Date]) , MAX([Transaction Date])))If you drag these onto your table is should do this for each tracking number.Sorry I just saw that it needs to not be blank as well. Try creating this measure:
Zone = // Create Table Filtered by blanks VAR __table1 = FILTER('Your Table',[Zone]<>Blank()) // Create a table that just holds the record we want VAR _table2 = FILTER(_table1,max('Transaction Date/Time')) //Get the zone VAR _zone= calculatetable(_table2,max([Zone]) return _zoneYou should be able to do the sasme with weight, just replace the [Zone] with [Weight] and rename _zone to _weight.
1) I am getting a syntax error for 'return'. Thoughts?
Hmm, that's interesting. Make sure I didn't something silly like leave out a paranthesis somewhere. I'm not exactly sure what would cause that. can you post a screenshot?
2) I don't understand the max(zone) part. I am not looking for the largest zone, I am looking for the zone that corresponds to the most recent date.
When we write measures, the have to be aggregated. We already narrowed the table essentially down to just one row, so there will only be one value. We just put max so that the measure will accept it.
Hi Anonymous ,
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
