Forum Discussion

WinterMist's avatar
WinterMist
Impactful Individual
4 years ago

Use Measure in Calculated Column

Hello - 

 

I have a scenario where users need to be able to select a percentage value from a slicer.

That selected slicer value is then used by DAX forumulas to update values in measures and calculated columns.

 

1) User selects 58.5% from the slicer.

2) I created a measure to pull the selected value: S Factor = SELECTEDVALUE('SRAF'[SRAF])

3) As a test, I pulled the above measure into a card to ensure that it works properly; which it does.

   The screenshot below shows that .585 is selected in the slicer, and .585 is returned in the card measure.

   If the slicer is updated to any other value, the card measure immediately updates to reflect it.

   So far so good.

 

 

4) There is a table called 'XL Comm Rates'  (screenshot below).

   The first 4 columns shown come from loaded data.

   The last 3 columns are calculated columns: Effective Rate, Profit Rate, S Factor

   NOTE: "S Factor" is actually not needed in the table.  It is only present for trouble shooting.

 

 

5) Both "Effective Rate" & "Profit Rate" calculated columns use the S Factor ONLY IF [Capped] = "Y".

   IF [Capped] = "N" THEN S Factor is not used in the calculation.

 

Effective Rate =
IF (
'XL Comm Rates'[Capped] = "Y",
'XL Comm Rates'[Base Rate] * 'XL Comm Rates'[Effective Rate Multiplier] * 'All Measures'[S Factor],
'XL Comm Rates'[Base Rate] * 'XL Comm Rates'[Effective Rate Multiplier]
)
 
Profit Rate =
IF (
'XL Comm Rates'[Capped] = "Y",
'XL Comm Rates'[Base Rate] * 'XL Comm Rates'[Profit Rate Multiplier] * 'All Measures'[S Factor],
'XL Comm Rates'[Base Rate] * 'XL Comm Rates'[Profit Rate Multiplier]
)
 
RESULT: As you can see from the screenshot above:
   - Effective Rate & Profit Rate get populated correctly whenever [Capped] = "N" because S Factor is not used.
   - But for every row where [Capped] = "Y", S Factor is used, and all 3 calculated columns return nothing.
   - It is strange to me that the card measure shows that the measure is populated correctly as .585, while at the same time, the exact same measure ('All Measures'[S Factor]) returns nothing when used in a calculation or calculated column.
 
I have read several forumns on measures & calculated columns being different, and I thought that I understood this.
That is why I am specifically using a measure to pull the slicer value, and calculated columns for row specific items within a table.  Clearly I am still missing something.
 
Thanks in advance for the help.
Need Help

7 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    4 years ago

    Calculated columns are populated at refresh time.  If using a measure in a calculated column (which we should not do), the measure will have whatever value powerbi gives to it at the same time.  I don't really want to get into the value it gets because it's not a good idea to use measures in this way.

    The calculated column value will not change when a slicer value changes.

    • WinterMist's avatar
      WinterMist
      Impactful Individual
      4 years ago

      HotChilli - 

       

      Thanks for educating me that the path I've been pursuing is not a good one.

      Is there an alternate path you would recommend for this situation?

       

      Business needs to be able to select continuously changing percentage values from a slicer (potentially many times per hour).  Each time the percentage value is updated, both Effective Rate & Profit Rate formulas need to immediately (not at refresh time) update their calculated values within the table visualization.

       

      Thanks again for your time.

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, WinterMist ;

    Slicers cannot be used in calculated columns.  They can, however, be used in measures.

    Slicers are Filters, and thus use filter context.  Filters can change dynamically based on what is selected, and how filter interactions are defined.  Since measures are dynamic fields calculated on demand, they can leverage this filter context to shape results.

    Then we get to calculated columns.  Calculated columns use row context.  They are populated once at data refresh, and don't update again.  If you change interactions, change slicers, the underlying row context and any calculations performed at load would be unaffected.

     

    So, you should use a measure instead of the calculated column if you would like to change the calculation in a table when the slicer value changed. as follows:

    Effective Rate1 = 
var _value=MAX('XL Comm Rates'[Base Rate])*MAX('XL Comm Rates'[Effective Rate Mutplier])
return 
IF (
MAX('XL Comm Rates'[Capped]) = "Y",
_value * 'SRAF'[S Factor],_value)
    Profit Rate1 = 
var _value=MAX('XL Comm Rates'[Base Rate]) * MAX('XL Comm Rates'[Proft Rate Mutiplier] )
return 
IF (
MAX('XL Comm Rates'[Capped]) = "Y",
_value*[S Factor],_value)

    The final output is shown below:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • WinterMist's avatar
      WinterMist
      Impactful Individual
      4 years ago

      Hello v-yalanwu-msft

       

      Thanks for educating me.

      I created the 2 measures you provided.

      However, when adding either of them to the existing table visual, PBI returns this error:

      “…not enough memory to complete this operation…”

       

       

      After looking at several sites on this PBI error, I checked the following things:

      NOTE: Screenshots are provided for each item below.

      • Is PBI Desktop the 16 bit version?  Yes.
      • Is PBI Desktop available memory set to around 16GB?  Yes.
      • Is the data model a star schema (Facts table with many dimensions)?  Yes.
      • Are there more than 15 columns in the table visual?  No.
        • There are currently 13 columns prior to adding either of the 2 new measures.
        • However, none of these 13 can be removed as business requires all of them.

       

       

       

       

       

       

       

       

       

       

      The report has not had memory issues up to this point that I have seen.

      Let me know if you have any suggestions.

       

      Thanks again for your help.

      • WinterMist's avatar
        WinterMist
        Impactful Individual
        4 years ago

        Additional Notes:

         

        1) The "XL Comm Rates" data table only has 33 rows.  If I create a visual table of only "XL Comm Rates" and add the 2 new measures, it works.  There is no memory error.

         

         

        2) However, "XL Comm Rates" table joins to the "D Plan" table (Plan Dimension) on the Plan field, and then from "D Plan" table back to the Facts table.

         

         

        3) The visual table where these 2 new measures need to be added is extremely large, as it contains data from Facts, and almost all dimensions.  For each row, it should lookup the "Plan" in the "XL Comm Rates" table to determine if the record is capped (Y/N) and then perform the calculations.  But of course, this is the visual table which produces the "not enough memory" error when attempting to add either measure.

         

         

         

        These notes should at least tell you that your measures are working correctly.

        Unfortunately, I just can't figure out how to use them in the visual table where business needs them yet.

         

        Thanks again for your time.