Use DAX Query to create table in Power BI

Question

I have a DAX Query that evaluates into my expected data in both Power BI Desktop and the online service. I am trying to use that same DEFINE/Evaluate DAX query in order to populate a table for my Semantic Model...But presently the only way I see to being able to use this data is by copying the result of the query manually. Is there a way to tie in my DAX Query into my data model cohesively?

As of this writing I know that there is limited scope of support for Dax Query beyond data processing/evaluation...But my interest here is that DAX allows me to pull my semantic model data into a result.

The code specifically I am using is

    VAR _tables = 
        SELECTCOLUMNS(
            FILTER(
                INFO.TABLES(),
                // Exclude hidden tables 
                [IsHidden] = FALSE()
            ),
            "TableID",[ID],
            "TableName",[Name]
        )
    VAR _columns = 
        FILTER(
            INFO.COLUMNS(),
            // Exclude RowNumber columns
            [Type] <> 3
            ) 
    
    VAR _result = 
        SELECTCOLUMNS(
            NATURALINNERJOIN( 
                _columns,
                _tables
            ),
            "Table",[TableName],
            "Column",[ExplicitName],
            "Description",[Description],
            "Column in Data Source",[SourceColumn],
            "Data Category", [DataCategory],
            "Column Type", 
                SWITCH(
                    [Type],
                    1,"Data column", 
                    2, "Calculated column",
                    [Type]
                ),
            "DAX formula", [Expression]
        )
EVALUATE
    _result

Sam Nseir · Accepted Answer · 2024-12-03 00:25:27Z

1

The DAX INFO functions cannot be used within Calculated Tables or Columns, ie cannot be used within the Semantic Model.

However, you can use them from within Power Query when connecting to SQL Server Analysis Services database. As an example:

Points to note:

You will need Premium per-user licence for the workspace or higher, to be able to connect to your workspace (you can get the Server connection link in Workspace settings).
The result of the Power Query will be of that of what is currently published in the Workspace. Meaning it will not pick-up and new changes as you publish them. The subsequent model refresh will bring this to parity.

You may find the following Power Query step a useful one to normalize your column names. It replaces the names with what is within the square brackets.

#"Fix Column Headers" = Table.TransformColumnNames(Query1, each Text.SplitAny(_, "[]"){1}),

answered Dec 3, 2024 at 0:25

Sam Nseir

13.1k2 gold badges5 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

SomekindaRazzmatazz Dec 3, 2024 at 16:56

Thank you for the insights here! Could you please clarify for me, you are using the Sql Server Analysis Server connection under the Database category in Power Bi's connectors and populating with the API connections for Power BI? Is this the appropriate documentation for setting up such fields?: learn.microsoft.com/en-us/rest/api/power-bi

Sam Nseir Dec 3, 2024 at 23:52

This is possible via XMLA endpoint - see Semantic model connectivity with the XMLA endpoint and radacad.com/…

Collectives™ on Stack Overflow

Use DAX Query to create table in Power BI

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related