How to convert MDX code to DAX code using import connection

[o59393]

Hi all

I need to connect to SAS database using a dax code.

How can I convert this MDX code below I have into dax?

SELECT {
[Measures].[Unit Cases AC],
[Measures].[Unit Cases RE]

}

ON COLUMNS,

NON EMPTY(
{[Ship From].[Operation].&[Central]}*
{[Ship From].[Region].&[Colombia/Venezuela]}*
{[Ship From].[Country].CHILDREN}*
{[Ship From].[Bottler Owner].CHILDREN}*
{[Ship From].[BU Ship From].CHILDREN}*

{[Period].[Month 445].CHILDREN}*
{[Period].[Year 445].&[2021]}*
{[Product].[L1.7 - Segment].CHILDREN}*
{[Product].[L1.5 - Category].CHILDREN}*
{[Product].[L1.3 - Trademark Category].CHILDREN}*
{[Product].[L1.1 - Beverage Product].CHILDREN}*

{[Package].[Container Material].CHILDREN}*
{[Package].[Container Type].CHILDREN}*
{[Package].[Primary Container Volume Capacity].CHILDREN}*
{[Package].[L1.4 - Refillability].CHILDREN}*
{[Package].[L1.5 - MS-SS].CHILDREN}*
{[Package].[BPP Code].CHILDREN}

) ON ROWS

FROM [Global]

WHERE (
{[Ship From].[ISSCOM Ship From].[Segment].&[Latin America]},
{[Sales Type].[Primary Sales Indicator].&[Y]},
{[Reporting View].[Reporting View].&[Operational View]}
)

Thanks!

[o59393]

Hi @TomMartens  and @Greg_Deckler 

Please forget the MDX part. I used live connection to the server:

Now, what I need is to create a single table taking some columns from a few tables below.

The reason of this merged table is to facilitate the model I want to create.

I used summarize to bring up a new table like this:

My question would be, how can I bring columns from different tables into my summarize function?

Below in green I have all the columns from [Package table] that are already in my new table above, but how can I incorporate the [Product table] columns for example? From what I see summarize can only work with one table at the time.

Thanks!

[Anonymous]

Hi @o59393 

I think Power BI Desktop doesn't support you to build calculated tables by dax if you use Live connection mode.

If you want to build calculated table by summarize, you can try import mode.

And if you want to get columns from other tables, for example you use summarize to get all rows from 

[Package table], then you want to calculate other columns from  [Product table]. You need to make sure there are related key columns in two tables like ID or others.

Then if you have build relationships between two tables. You can use related function.

Table = 
Summarize([Package Table],...
...
...
...,
"Column1 from [Product Table]",Related[Column1]
...
)

If you didn't build relationship between two tables. Try this code:

Table = 
Summarize("Package",...
...
...
...,
"Column1 from [Product Table]",calculate(sum/max("Product"[Column1]),Filter("Product","Procuct"[KeyColumn] = Earlier("Package"[KeyColumn])))
...
)​

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@o59393 I would refer you to this artcile: Converting MDX to DAX – First Steps - SQLBI

[TomMartens]

Hey @o59393 ,

in Power BI Desktop, create a matrix visual.

Drag the measures (in MDX ... on columns) to the values bucket of the matrix visual.

Drag the columns to the rows, you have to filter for dedicated elements e.g., 'Central' in the visual level filter.

Create Slicers for all the columns used in the where section of the MDX query.

This should do the trick already.

Regards,

Tom 

[o59393]

Hi @TomMartens 

Not sure I understand, let me give some context.  I need to use a live connection and dax to create the table I need to import from the server.

This table is composed/merged of different ones that are held in an azure database.

Can my code above be transformed into dax? How would the syntax be then?

Thanks!

[TomMartens]

Hey @o59393 ,

from the screenshot of your initial post, it seems you are using an MDX statement to connect live to SSAS or are you really connecting to a SAS server.

After the connection is established, how does the field list look like?

You can't use DAX to connect to an SSAS Multidimensional server.

Why do you want to use DAX to create a table?

Regards,

Tom