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 calcu...
Need Help
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.

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

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's avatar
    WinterMist
    Impactful Individual
    4 years ago

    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.