Filter one table by the inclusion of data in another table with DAX

[leomcc]

Hi, I have a (what I think should be) simple problem, but I cannot find a straightforward DAX solution on this forum or otherwise.

I have a table with a list of whole numbers (Staff ID), and a table with a list of Projects, some of which are owned by the aforementioned Staff. I have a DAX expression that can filter the table by a single Staff ID, but I cannot find a way to filter one table by the other table of Staff ID (or a list).

In Excel I would use the following expression, but I cannot seem to get this to translate appropriately:

=FILTER(ProjectsByWBS, COUNTIFS(StaffTable, ProjectsByWBS[Staff Number]))

Current DAX expression (for a single Staff Number):

Chargeouts = SELECTCOLUMNS(FILTER(ProjectByWBS, [Staff Number]=5678), "Staff Number", [Staff Number], "Project ID", [Project ID], "YTD", [YTD])

Sample Data for the 'Staff Table' and 'ProjectByWBS' (the project table)

Staff Number

1234

5678

9100

Staff Number	Project ID	Cost YTD
5678	A	10
2355	B	5
9100	C	8

Sample output table:

Staff Number	Project ID	Cost YTD
5678	A	10
9100	C	8

Any help would be greatly appreciated! At this point I am just trying random combinations of functions because I cannot find an excel-like solution.

[barritown · Accepted solution]

Hi @leomcc,

I guess you need to create a new table with such a line of [DAX] code:

In plain text:

Chargeouts = FILTER ( ProjectByWBS, [Staff Number] IN VALUES ( Staff[Staff Number] ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[leomcc]

Thanks for your help - I actually figured this out earlier, but my solution matches yours!