CODEWITHSUNDEEP
daxpowerbi
cuongle
cuongle

Reputation: 75306

Get list of joined rows with DAX

I have Supplier dimension table has 1:n relationship with InvoiceDetail fact table. I would like to get the list of active suppliers like below SQL, but in DAX language:

SELECT [Id]
  ,[Name]
  ,[Code]
  ,[CountryIso]

FROM [Supplier] s
WHERE EXISTS (SELECT 1 FROM [InvoiceDetail] id WHERE s.id = id.SupplierId)

I am not sure how I can do on Measure with DAX

Upvotes: 0

Views: 104

Answers (1)

Frostytheswimmer
Frostytheswimmer

Reputation: 718

Assuming that an active supplier means that the supplier has an invoice against them and that your data looks something like this..

Invoice Table

Invoice Table

Supplier Table

Supplier Table

Creating a relationship between the two tables will in effect, 'join' the two tables.

Joining the Tables

You can then use invoice number field from the invoice table and the name/code/countryiso from the supplier table.

Example being:

enter image description here

The value are only being drawn from the invoice table, so you'll only see active Invoices.

If being an active supplier means having a true bool value, join the tables and add a report/page wide filter on that bool value.

Upvotes: 1

Related Questions