Forum Discussion

pade's avatar
pade
Icon for Advocate III rankAdvocate III
5 years ago

Query Folding code for: SELECT * FROM T1 WHERE EXIST (SELECT 1 FROM T2 WHERE T1.C=T2.C)

I'm looking for a M-code that minic this SQL code: SELECT * FROM DimensionTable as d WHERE EXISTS (SELECT 1 FROM FactTable as f WHERE f.customerId = d.userId)   Edit: Please note that Query Fol...
Tips and Tricks
v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, pade 

 

Based on your description, I created data to reproduce your scenario, The pbix file is attached in the end,

Dimension:

 

Fact:

 

Power Query:

As is suggested by wdx223_Daniel , you may create a new query with the following codes in 'Advanced Editor'.

let
    Source = Table.SelectRows(Dimension,each List.Contains(Fact[CustomerId],[UserId]))
in
    Source

 

Result:

 

DAX:

You may create a calculated table as below.

Table = 
FILTER(
    'Dimension',
    [UserId] in DISTINCT('Fact'[CustomerId])
)

 

Result:

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

pade's avatar
pade
Icon for Advocate III rankAdvocate III
5 years ago

Thanks v-alq-msft .

 

Although, I can't use this solution either, and I realize it's my fault. I didn't mention that I'm using Direct Query mode, hense I can't create calculated tables.

I will update my initial post so this is more clear