Simple SQL equivalent in DAX or MDX

Question

question

AJ-AJ asked Jul 29, '21 CarrinWu-MSFT commented Aug 2, '21

Simple SQL equivalent in DAX or MDX

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.

sql-server-analysis-services

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2021-07-30T05:56:25.143Z

CarrinWu-MSFT answered Jul 30, '21 CarrinWu-MSFT commented Aug 2, '21

Hi @AJ-AJ,

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.

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AJ-AJ · Jul 30, 2021 at 04:34 PM

TThanks

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.

0 ·

CarrinWu-MSFT AJ-AJ · Aug 02, 2021 at 02:18 AM

Hi @AJ-AJ, sorry about that I am not familiar with DAX, please refer to Alexe's answer.

0 ·

Answer 2 · 2021-07-30T13:20:04.837Z

AlexeiStoyanovsky answered Jul 30, '21

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2021-07-30T13:33:25.893Z

AJ-AJ answered Jul 30, '21 AlexeiStoyanovsky edited Aug 1, '21

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.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AlexeiStoyanovsky · Jul 30, 2021 at 06:15 PM

Actually the IN operator has been a part of DAX for quite some time now. See my answer above.

0 ·

question