CODEWITHSUNDEEP
filterdax
Colin
Colin

Reputation: 3

Power BI match one field in another table, then return a different field from same record and filter multiple results

Task: calculated Table A contains 2 columns from Table B, and a filtered column from Table C that is based on values from a column in Table A.

Table A Work Pack Job Job Start Date 123 00A (value from column "Start Date" in Table C, matches Job)

Problem: I cannot figure out DAX to populate [Job start date].

DAX to populate Work Pack and Job

Table A = SUMMARIZE (
   'work history'[work pack]
   'work history'[job]
)

I have tried variations around FILTER but cannot deduce how to provide [job] as a key, then filter on the multiple values for [Job start date]. Some jobs have many start dates and I want only the first.

If this was SQL it would be

SELECT * From TableC where [job no] = TableA.[job no]
   LIMIT 1
   ORDER BY [Job start date]

DAX is a new thing for me and currently it's a struggle to do stuff that would be a 10 minute job in Excel or Access.

Edit: this site is pretty close to what I had in mind but the solution is to generate a table, not a column:

https://www.sqlservercentral.com/articles/dax-for-sql-folks-part-ii-translating-sql-queries-to-dax-queries

Upvotes: 0

Views: 1394

Answers (1)

SamBouKoa
SamBouKoa

Reputation: 206

Try this:

Table A = SUMMARIZE ('work history'
   'work history'[work pack],
   'work history'[job],
   "TableCCol",
   max('TableC'[Column])
  )

Upvotes: 0

Related Questions