Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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])
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |