Forum Discussion

smpa01's avatar
smpa01
Community Champion
3 years ago
Solved

Minify DAX

My current code is like this

 

EVALUATE
VAR base ={1}
RETURN base

 

Is there any tool available that kills all the code line brakes.

 

EVALUATE VAR base = {1} RETURN base

 

I am writing a dynamic AS query for which I need the DAX to be minified cause it is much easier to inject the parameter in the minified version.

 

let x = "cat1",
    y = Analysis.ServiceDataBase("serverString","db",[Query="...FILTER(tbl,tbl[Category]="&x&"..)])

  in 
    y

 

GilbertQ d_gosbell 

Thank you in advance

Need Help
  • d_gosbell's avatar
    d_gosbell
    3 years ago

    You don't need to minify anything since you can use multi-line string variables in Power Query
    eg

    let
    SourceQuery = "
EVALUATE
'Product Category'",
    result = AnalysisServices.Database("localhost\tab19", "Adventure Works", [Implementation="2.0", Query=SourceQuery])
in
    result

2 Replies

  • d_gosbell's avatar
    d_gosbell
    Super User
    3 years ago

    You don't need to minify anything since you can use multi-line string variables in Power Query
    eg

    let
    SourceQuery = "
EVALUATE
'Product Category'",
    result = AnalysisServices.Database("localhost\tab19", "Adventure Works", [Implementation="2.0", Query=SourceQuery])
in
    result
  • smpa01's avatar
    smpa01
    Community Champion
    3 years ago

    Regex is a possible solution

     

    const regex = /\n/gm;
const str = `--  SUBSTITUTEWITHINDEX is a tool function used mainly by
--  Power BI to map values in a query to column in a matrix
--  by substituting the index columns with a number indicating
--  the column number where to put the result.
--  The matching between the two tables is based on data lineage
--  or column names.
DEFINE
    VAR R =
        SUMMARIZECOLUMNS (
            'Product'[Brand],
            'Date'[Calendar Year],
            TREATAS ( { "Contoso", "Fabrikam" }, 'Product'[Brand] ),
            "Amount", [Sales Amount]
        )
    VAR C =
        SUMMARIZE ( Sales, 'Date'[Calendar Year] )
    VAR C_ColumnName =
        SELECTCOLUMNS({"CY 2007", "CY 2008", "CY 2009"}, "Calendar Year", [Value])
    VAR Result =
        SUBSTITUTEWITHINDEX ( R, "Column #", C, [Calendar Year], ASC )
 
EVALUATE R
 
EVALUATE C
 
EVALUATE C_ColumnName
 
EVALUATE Result
 
-- The following code would generate an error 
-- because C_ColumnName has the same name
-- of a column with a data lineage
-- EVALUATE
-- SUBSTITUTEWITHINDEX ( R, "Column #", C_ColumnName, [Calendar Year], ASC )
 
-- The following code works because the 
-- Date[Calendar Year] column loses the data lineage
-- in SELECTCOLUMNS by using an expression
EVALUATE
SUBSTITUTEWITHINDEX (
    SELECTCOLUMNS (
        R,
        'Product'[Brand],
        -- the expression remove the data lineage
        "Calendar Year", 'Date'[Calendar Year] & "",
        [Amount]
    ),
    "Column #", C_ColumnName, [Calendar Year], ASC
)`;
const subst = ` `;
const result = str.replace(regex, subst);
console.log('Substitution result: ', result);