Data Modelling in PBI for multiple conditional joins

[Anonymous]

Hi,

I am pretty new to PBI and data modelling. Instead of using SQL views i tried to join the tables directly in PBI and it is working perfectly. But now I have got the below scenario. In Merge I can select the column or multiple columns for joining but how can i define the below highlighted while merging my tables.

From

 dbo.IncidentDimvw I
 
 INNER JOIN dbo.WorkItemDimvw WI
 ON I.EntityDimKey = WI.EntityDimKey 

 LEFT OUTER JOIN 
 dbo.WorkItemCreatedByUserFactvw
 ON dbo.WorkItemCreatedByUserFactvw.WorkItemDimKey = WI.WorkItemDimKey
 AND WorkItemCreatedByUserFactvw.CreatedDate =
(
Select Max(CreatedDate) from WorkItemCreatedByUserFactvw where WorkItemDimKey = WI.WorkItemDimKey
)

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

You could get by two merge steps as below:

First merge

Step1: 

In Merge function, merge WorkItemDimvw table and WorkItemCreatedByUserFactvw table

Step2:

Expand CreatedDate column and then use Group By to get Max(CreatedDate) for each WorkItemDimKey 

Second merge

Now merge WorkItemDimvw table and WorkItemCreatedByUserFactvw table by two conditional

dbo.WorkItemCreatedByUserFactvw.WorkItemDimKey = WI.WorkItemDimKey

and Max(CreatedDate) = WorkItemCreatedByUserFactvw.CreatedDate

Best Regards,

Lin