Use "WHERE IN" in DAX to filter text field

[Anonymous]

Hi, I have 2 tables as Employee and Product and I have a column called Role in the Product table and the Employee table. I'm using Role as a filter and when I select Role, the linked Products will come from the Products Table. With that products, I need to filter the same products from the Employee table.

Basically, In SQL terms, I have to pass the Product field in the Product table as a parameter to filter the same Products in the Employee Table.  

Example:

WHERE Employee.Role IN (Product.Role)

Can we use the same "WHERE IN" in DAX Measure?

[MFelix · Accepted solution]

Hi @Anonymous,

Not really sure on what you want if it's a measure or a calculated column or a table.

But you can for example create a dimension table with Roles and make a relationship between both tables, or using the LOOKUP formula to return the Employee name base on the roles.

In the query editor you should do a merge of the tables.

If you give more insight, example data and expected result I can help you better.

Regards,

MFelix

[Anonymous]

Hi @MFelix

Thanks for your reply.

basically, I'm looking for the below SQL to be converted as DAX

Select Product from Employee
Where Product IN (Select ProductName from Product where Role = @FunctionalRole)

@FunctionalRole is my slicer. By selecting the Role using the slicer, the related products in the Employee table has to be slected.

Thanks

Akhil

[Anonymous]

Can you Please give me the dax query which looks like this

i.e,

select products from productstable where products in(select products from customer where customerid=@customerid)

Thanks

[MFelix]

Hi @Anonymous,

Correct me if I'm wrong if you have two tables Product and Employee on PBI, then just create a dimension table for the Roles with distinct values and make a relationship between this 3 tables  then on your tables measures calculated columns use the Roles table to make your filter.

Regards,

MFelx