Joining 2 tables with condition

[Anonymous]

Hi guys. Hope you can help. I am trying to join 2 tables in PBI with a filter/condition.
I have a SQL syntax but struggling to convert it to DAX (PBI).
Table1:CHECKEDTICKETS
Table2:TRANSACTIONS

SQL look slike this:

"...WHERE
( CHECKEDTICKETS.TICKETNO = TRANSACTIONS.RECEIPT_NO and CHECKEDTICKETS.CHT_CAL_ID between
TRANSACTIONS.CAL_CAL_ID and TRANSACTIONS.V_CAL_CAL_ID )
GROUP BY
CHECKEDTICKETS.TICKETNO

TRANSACTIONS.CAL_CAL_ID and TRANSACTIONS.V_CAL_CAL_ID are basically dates but data type is number.

[amitchandak]

Assuming CHECKEDTICKETS is a master table and has a primary key key1. lease add index column here in power bi

populate that in TRANSACTIONS

CHECKEDTICKETS Key in TRANSACTIONS = minx(filter(CHECKEDTICKETS ,CHECKEDTICKETS[TICKETNO] = TRANSACTIONS[RECEIPT_NO] && CHECKEDTICKETS[CHT_CAL_ID] >=
TRANSACTIONS[CAL_CAL_ID] && CHECKEDTICKETS[CHT_CAL_ID]<= TRANSACTIONS[V_CAL_CAL_ID ]),CHECKEDTICKETS [Key1])

[Anonymous]

Table =
NATURALLEFTOUTERJOIN (
  CHECKEDTICKETS,
    FILTER (
        CROSSJOIN ( CHECKEDTICKETS, TRANSACTIONS ),
        CHECKEDTICKETS[TICKETNO] = TRANSACTIONS[RECEIPT_NO]
        CHECKEDTICKETS[CHT_CAL_ID] >= TRANSACTIONS[V_CAL_CAL_ID]
            && CHECKEDTICKETS[CHT_CAL_ID] <= TRANSACTIONS[V_CAL_CAL_ID]
    )
)

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos. 

[Anonymous]

Or You may refer to the following DAX that adds a calculated column.

Check below link 

https://community.powerbi.com/t5/Desktop/conditional-join/td-p/339960

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.