Equivalent of SQL subquery

Question

Hi,&nbsp;I have a simple table Sales with the daily sales per client:day &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; client. &nbsp; &nbsp; &nbsp; &nbsp; spentUSD20200501. &nbsp; &nbsp;a. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 500020200501. &nbsp; &nbsp;b. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;49020200502. &nbsp; &nbsp;b &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3000....&nbsp;I need to build a dashboard where the user can select two days and they can see the clients gained/lost:&nbsp;- two tables: one with the clients gained (in the example above empty) and another table with the clients lost (client #a)- two cards: showing the number of clients gained (0) and lost (1)&nbsp;Conceptually it's very straight-forward, but I cannot make it work. In SQL I'd use something like :select * from Sales where day = $selectedDay1 and client not in (select client from Sales where day = $selectedDay2)&nbsp;Any ideas? I tried using "NOT IN" but couldn't make it work with dynamic values (the ones in the slicers).&nbsp;Thanks,

camargos88 · Accepted Answer

Hi&nbsp;ubv&nbsp;,&nbsp;Check this link:&nbsp;https://www.daxpatterns.com/new-and-returning-customers/&nbsp;Did I answer your question? Mark my post as a solution!Ricardo

Forum Discussion

Equivalent of SQL subquery

2 Replies

Related content

DAX equivalent of SQL's NOT IN?

DAX equivalent of SQL LIKE

ERROR ADBC: InternalError [] [Snowflake] 002031 (42601): SQL compilation error: Unsupported subquery

DAX equivalent of SQL Coalesce

DAX SQL window function equivalent