Multi-Conditional Lookups

[Anonymous]

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?

[v-piga-msft]

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

[Anonymous]

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
_zone

You should be able to do the sasme with weight, just replace the [Zone] with [Weight] and rename _zone to _weight.

[Anonymous]

Thanks for your reply! Two questions/comments:

1) I am getting a syntax error for 'return'. Thoughts?

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. 

[Anonymous]

---

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.

[Anonymous]

You were correct, there was a missing parenthesis - my fault entirely. 

Here is the current error I am receiving: 

[Anonymous]

Oops, try changing

calculatetable(_table2,max([Zone]))

to 

sumx(_table2,[Zone])

[Anonymous]

I tried your solution and now I'm having a new issue. 

While it did produce error-free results, the problem is it is now summing all the zones in table2. 

I am unsure how to proceed @Anonymous 

If I try the second option by @Iamnvt 

[Anonymous]

Sorry about that, let me take another crack at it. Swapping out the sumx() line again:

calculate(max([Zone]),_table2)

I think my original mistake was using calculatetable() where a calculate() was supposed to be. 

[Iamnvt]

Non-blank, lastdate zone = 

VAR _table2 = CALCULATETABLE(FILTER(Table1, Table1[date] = MAX(Table1[date])), Table1[Zone] <> BLANK())

return
CALCULATE(MAX('Table1'[Zone]), _table2)

look likes you entered a different measure than my measure.

There is no _table1 there

@Anonymous 

[Iamnvt]

hi,

@Anonymous 

you can try this Measure:

Non-blank, lastdate zone = 

VAR _table2 = CALCULATETABLE(FILTER(Table1, Table1[date] = MAX(Table1[date])), Table1[Zone] <> BLANK())

return
CALCULATE(MAX('Table1'[Zone]), _table2)

here is PBI file:

https://1drv.ms/u/s!Aps8poidQa5zk6sAwEZ4y0m_xZfwWg