Use Measure in Calculated Column

[WinterMist]

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.

[v-yalanwu-msft]

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]

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.

[v-yalanwu-msft]

Hi, @WinterMist ;

check how much RAM and CPU power BI is taking. Sometimes web browsers take a lot of memory.  So please check and close any applications taking a lot of memory.

Save changes in power BI too and close and open the file again and try.

It may be caused by the data model being too bloated if there are no other services occupying hardware resources. For example, the data table is too large, or there are too many queries...

In addition, the local machine configuration is too low, which is also a problem.

Try to refer to the links.

https://radacad.com/performance-tip-for-power-bi-enable-load-sucks-memory-up

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]

Hello v-yalanwu-msft 

Thanks for your response.

• Prior to adding one of the new measures to the table visual, this is a snapshot of PBI CPU & Memory usage:
• CPU = 1.2%
• RAM = 1 GB

• When attempting to add one of the new measures to the new visual table, PBI spins & Memory just keeps growing the longer I wait.
• 00m 00s – 1 GB (34%)
• 01m 00s – 8 GB (56%) screenshot
• 02m 00s – 13 GB (72%)
• 03m 00s – 17 GB (85%) screenshot
• 03m 30s – 19 GB (89%)
• 04m 00s – 21 GB (94%) screenshot
• For the next 10 minutes, it fluctuates between 97-99% at around 25 GB before the PBI visual table eventually crashes & shows “Can’t display the visual.”
• At that time, CPU & Memory return to normal levels again.

ADDITIONAL NOTES:

• Since this table visual includes the Policy Number from the Facts table, it’s essentially pulling all the records from the Facts table (with only 2 filters):
• Product = [Main Product]
• Year >= 2017
• As a result, if I set Slicer Year = 2021, there are ~25K records in the table just for that one year.  Not surprising.  And there are several years to select from in the Year slicer.
• But this is to be expected as it’s what business wants to see and has been using for a long time now, without any issues.
• So if no slicer values are selected, this table visual shows hundreds of thousands of fact records, along with many dimension columns.
• If I export from the table visual to CSV for 2021, it will export the list (~25K records).  However, if I don’t filter on year & attempt to export, it gives the following error:
• Data exceeds the limit.  Your data is too large.
• I don’t believe this is the cause of my measure memory error, unless the problem is merely the number of rows. 
• I’m simply sharing this to show that the number of rows in the table visual is large, and is to be expected.

• I’m not seeing anything wrong with the data model, that I’m aware of.  A star schema is designed to have a very large Facts table, with a usually continuously increasing number of records.  In previous roles, I have seen Facts tables with millions of records.  So this example seems comparatively small to me.  (At that time, however, I was using QlikSense, and not Power BI though.)
• Is it truly not possible to add a measure calculation to this table visual, simply because there are hundreds of thousands of rows?
• I’m hopeful that the problem is with my ignorance, and not simply a number-of-rows limitation with PBI.

Thanks again for your time.

[WinterMist]

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.

[HotChilli]

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]

@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.