Simple SQL equivalent in DAX or MDX

Question

Hi there,

I'm looking DAX or MDX equivalent for something similar to the below SQL query.

Select * from OrderTable
where orderno in (100,200)

Currently I have a tabular model cube deployed.

DAX query

--Returns all the records and columns
Evaluate 'OrderTable'

--Returns only 2 records based on the condition
Evaluate (Filter('OrderTable','OrderTable'[orderno] = "100" || 'OrderTable'[orderno] = "200" ))

The above is hardcoded version. Any suggestions on how to make it dynamic? what if 10 orders separated with comma are to be searched via the website textbox.
Is there anything like that would just receive a comma seprated values

Evaluate (Filter('OrderTable','OrderTable'[orderno] = commaseperatedparameter ))

I'm open for MDX suggestions as well.

TIA.

Answer 1

Hi @RJ ,

Welcome to Microsoft Q&A!

Any suggestions on how to make it dynamic?

If you are trying to use MDX, you should create a calculated measure to return the dynamic value. Use a member function .CurrentMember, so you do not need to hardcode any specific member name. Please refer to this similar thread.

Best regards,
Carrin

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

This seems to fit: https://www.sqlbi.com/articles/the-in-operator-in-dax/

Answer 3

The current website setup is like below

Website passes parameter --> SQL/MDX commands saved in a .sql/.mdx file inside a folder --> content of the file is executed against the DB or cube. Connections are handled by the website.

The SQL file has only command to be executed. For example, the file contains only select * from orders where orderno = orderparameter(fromwebsite). The website sends comma values of orderno and it triggers a file. Same way, im looking for DAX to be saved as a file in a file folder and that the content of the file will be triggered. Any suggestions?

is there a direct DAX statement which I can save in a file and will work for 100,200.

EVALUATE FILTER ('Orders','Orders'[Orderno] = "100,200")) - this does not work. 100,200 will be the parameter. This would solve the problem.

My understanding is since DAX does not allow in as filter condition wondering how can i make it dynamic (100,200). Thanks for your help.

To make things clear, I gave orders as an example. Please dont get hijacked into thinking its fact, etc.. Im trying to query a tabular model TABLE which is hidden. However the query works. Just need the equivalent of WHERE COLUM IN (100,200). I dont need to calculate anything. Just trying to query a tabular model table.