Moving DAX Calculation into PQ for Query Folding

[Anonymous]

Hello,

I'm pretty new to Power Query, I have a table I'm pulling into PBI that was query folding back into SQL Server. I've needed to create a measure as a calculated column in PBI for some visualisations I'm being asked to create but this has broken query folding and has significantly increase refresh time. I need to create the below DAX as a Custom Column in PQ. 

Any help would be appreicated, below is the DAX I need to recreate. 

Thanks

Calculated Column = 

VAR _Column = DIVIDE(([VOLUME]-[COST]-[WASTE]),[VOLUME],0)

RETURN

SWITCH(
    TRUE(),
    _Column < 0, "< 0 %",
    _Column >= 0 && _Column < 0.10, "0-10 %",
    _Column >= 0.10 && _Column < 0.20, "10-20 %",
    _Column >= 0.20 && _Column < 0.30, "20-30 %",
    _Column >= 0.30 && _Column < 0.40, "30-40 %",
    _Column >= 0.40, "40 %+"
)

[BA_Pete · Accepted solution]

Hi @Anonymous ,

You can either create a new column in PQ for the variable, or you can include the variable calculation in each line of the new PQ column.

The first option would look like this:

// varColumn
([VOLUME] - [COST] - [WASTE]) / [VOLUME]

// newColumn
if [varColumn] < 0 then "< 0%"
else if [varColumn] < 0.1 then "0-10%"
...
...
else //your escape value

The second option would look like this:

// newColumn
if ([VOLUME] - [COST] - [WASTE]) / [VOLUME] < 0 then "< 0%"
else if ([VOLUME] - [COST] - [WASTE]) / [VOLUME] < 0.1 then "0-10%"
...
...
else //your escape value

Both options should generate a foldable CASE statement.

Pete