Self-Serve Extract Builder

This is a concept I made to enable users to self-serve an extract from a Power BI semantic model, in the same way certain tools sit on top of a database and provide a drag-and-drop interface to process big data into extracts for onward analysis.

I wanted to see if an intuitive interface could be built to allow a DAX query to quickly be constructed. The execution of DAX queries through Power Automate is generally very quick, and up to 5000 rows can be returned in a couple of seconds. The video above shows the building of a DAX query using the interface. The fairly recent addition of the DAX query view in Power BI Desktop means this can easily be tested.

Here is my Power FX code for generating the DAX:

With(
    {
        Dims:
        Concat(
            colSelDimensions,
            Definition & "," & Char(10) & Char(32) & Char(32) & Char(32) & Char(32) & Char(32) & Char(32)
        ),
        Mets:
        Concat(
            colSelMetrics,
            """" & Substitute(Metric, " ", "_") & """, " & Definition & "," & Char(10) & Char(32) & Char(32) & Char(32) & Char(32) & Char(32) & Char(32)
        )
    },
    IfError(
        "DEFINE" & Char(10) & Char(32) & Char(32) &
        Concat(
            colSelFiltersValues,
            "VAR __DS" & ID & "FilterTable = TREATAS({""" & Value & """}, " & Definition & ")" & Char(10)
        ) & Char(10) &
        "VAR __DS0CORE = " & Char(10) & Char(32) & Char(32) & Char(32) & Char(32) &
        "SUMMARIZECOLUMNS(" & Char(10) & Char(32) & Char(32) & Char(32) & Char(32) & Char(32) & Char(32) &
        Dims &
        Concat(
            colSelFilters,
            "__DS" & ID & "FilterTable," & Char(10)
        ) &
        Left(Mets, Len(Mets) - 8) & Char(10) & Char(32) & Char(32) & Char(32) & Char(32) &
        ")" & Char(10) & Char(10) &
        "EVALUATE" & Char(10) & Char(32) & Char(32) &
        "__DS0CORE",
        ""
    )
)

The unicode characters aren’t essential but they do mean the DAX looks attractive and readable when copied from the app. The approach makes use of the predictability of the layout of a DAX query, and thus is able to combine several Concat() statements to good effect.

As mentioned before, this was just a concept and not something I would necessarily put into production.