topic Re: Identify specific cell value based on row context and date in DAX Commands and Tips

Identify specific cell value based on row context and date

croufspesi — Mon, 26 Aug 2019 12:49:15 GMT

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

Re: Identify specific cell value based on row context and date

Anonymous — Mon, 26 Aug 2019 17:16:11 GMT

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.

Re: Identify specific cell value based on row context and date

Anonymous — Mon, 26 Aug 2019 17:18:23 GMT

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

Best
D.

Re: Identify specific cell value based on row context and date

Anonymous — Mon, 26 Aug 2019 17:53:27 GMT

Here's the M code...

Best

Re: Identify specific cell value based on row context and date

croufspesi — Fri, 30 Aug 2019 19:03:19 GMT

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.

Re: Identify specific cell value based on row context and date

Anonymous — Mon, 02 Sep 2019 20:42:00 GMT

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