Forum Discussion
Join on multiple columns using Power query
Anonymous I think you might be trying to apply the logic of some other app you are using to Power BI, and that will not work. You don't actually create JOINS in the Power BI data model. You do in Power Query, but not in DAX. In DAX they are filter relationships, and they either equal or they don't.
However, once you have a filter relationship, you can apply other logic in your formulas (measures) to modify how they work. For example, you might have the following to show cumulative sales through a date on your visual:
Sales Cumulative = VAR varCurrentDate = MAX( Sales[Date] ) RETURN CALCULATE( [Sales], Date[Date] <= varCurrentDate, REMOVEFILTERS( Dates[Date] ) )That will cause the filter to act in a way that will get all sales prior to today through today.
But that is not a merge operation.
Does that make sense?
Perhaps tell us what your end goal is, and not ask us to translate Program A logic into Power BI logic. It can cause us to come at this totally wrong, which I've certianly being doing so far. 😁
Thanks edhans for the response.
My sales fact table has relationship with multiple tables along with Purchase dim table. So i dont want to go and create multiple merge tables and use it in my model and create reports on top of it.
As my fact table is surrounded by multple dimension tables, I am looking for a better approach to resolve the join condition between the sales table and with the individual tables using the concatenate columns.
So as a first step, i am trying to replicate the join condition that occurs between the fact(Sales) and Purchase table.Like wise i am planning to do the same with fact and other dim tables. so that relation between dim and fact table occurs as 1:M and the data gets filtered accordingly.
Please correct me if my approach is wrong in doing so.
As part of impementation between the tables, I am unable to figure how to implement this logic from 2 tables
purchase.frm__dt (+)<= sales.dum_dt and
purchase.to_dt (+) >= sales.dum_dt
I tried using lookupvalue but unable to achieve on Dim table.
Any suggestion will be highly appreciated.
Thanks in advance.
Anonymous I think you might be trying to apply the logic of some other app you are using to Power BI, and that will not work. You don't actually create JOINS in the Power BI data model. You do in Power Query, but not in DAX. In DAX they are filter relationships, and they either equal or they don't.
However, once you have a filter relationship, you can apply other logic in your formulas (measures) to modify how they work. For example, you might have the following to show cumulative sales through a date on your visual:
Sales Cumulative =
VAR varCurrentDate =
MAX( Sales[Date] )
RETURN
CALCULATE(
[Sales],
Date[Date] <= varCurrentDate,
REMOVEFILTERS( Dates[Date] )
)
That will cause the filter to act in a way that will get all sales prior to today through today.
But that is not a merge operation.
Does that make sense?
Perhaps tell us what your end goal is, and not ask us to translate Program A logic into Power BI logic. It can cause us to come at this totally wrong, which I've certianly being doing so far. 😁
Anonymous - do you have a solution to this yet? If so, can you mark one or more posts as the solution. If not, can you get back to us with what you are trying to accomplish - the end goal - vs how to accomplish specific steps due to your familiarity with how another app does it?