Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Moving DAX Calculation into PQ for Query Folding

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 %+"
)

 

 

 

 

 

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    4 years ago

    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

1 Reply

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    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