Identify specific cell value based on row context and date

[croufspesi]

I've got a scenario where I can tell I'm close...but not quite there.  Tried combinations of things with FILTER, RELATED, and EARLIER but have not been successful.  Looking to identify the following:

For each BrochureCode on the Brochure_Reference table(1), would like Origin to be defined as the CityCode from the earliest start date on EventDetails Table (2) for that related brochure code.  Would like Destination to be the CityCode from the latest start date on the Event Details table (2) for that related BrochureCode.  I also need to exclude the CityCode of "PLW" from all origin and destination determinations.   

Table 1: BrochureReference

BrochureCode  |   Start Date   |  Origin  |  Destination  |

66407               |  12/3/18       |      ?       |           ?          |

66408               |  12/6/18       |               |                      |

66409               |  12/11/18     |               |                      |

Table2: EventDetails

BrochureCode  |  Start Date  |  CityCode  |

66407               |  12/3/18     |    NOL        |

66407               |  12/3/18     |    PLW        |

66407               |  12/4/18     |    MOB       |

66407               |  12/5/18     |    PEN        |

Solution for BrochureCode 66407 - Origin = NOL and Destination = PEN

[Anonymous · Accepted solution]

Here's the M code...

Best

D.

[croufspesi]

Thanks @Anonymous   I appreciate your help on this

I'm relatively new to trying this level of data modeling.  I've taken about 15 hours of online courses through Udemy on DAX/PowerBI, so DAX is just what I'm familiar with because of that course.

I can see that in PowerBI M is used... but wasn't sure if as a beginner, I should already be diving into another language and forgetting the one I just recently learned.

[Anonymous]

Why should you forget DAX while learning M? M is a data mash-up language, whereas DAX is Data Analysis eXpressions language. They serve very different purposes. In addition, you don't have to know M at all to use Power Query. Of course, it's always better to know what's going on under the hood but it's not strictly necessary.

Best

Darek

[Anonymous]

Give me a sec... I'll give you a full working solution in M.

Best
D.

[Anonymous]

Please use Power Query to do it, not DAX. DAX is not a language to mash up data. M is. DAX is a language to do data analysis, not to shape it.

And besides... in PQ it'll be much, much easier.

Best
D.