Search code examples
powerbidax

Use DAX Query to create table in Power BI

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
Solution

  • 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:

    Example screen shot

    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}),