Simple SQL equivalent in DAX or MDX

RJ 106 Reputation points
2021-07-29T20:06:28.99+00:00

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
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,249 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. CarrinWu-MSFT 6,856 Reputation points
    2021-07-30T05:56:25.143+00:00

    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.


  2. Alexei Stoyanovsky 3,416 Reputation points
    2021-07-30T13:20:04.837+00:00
    0 comments No comments

  3. RJ 106 Reputation points
    2021-07-30T13:33:25.893+00:00

    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.