Resourses Exceeded for Visuals

[Infidti]

Am at a bit of a sticking point only have around 1 mil rows of data but getting rescources exceeding for visuals. I have tried to filter down my data as much as i can but i simply can't go any lower than what i have now. I am displaying the following calcs in one table;

Date

buy <1yr= 

SUMX(

    Data,

    IF([Count] > 0 && [Count] < 12, 1, 0)

)

buy 1-3 yr= 

SUMX(

    Data,

    IF([Count] > 11 && [Count] < 37, 1, 0)

)

buy 3-5 yr=

SUMX(

    Data,

    IF([Count] > 36 && [Count] < 61, 1, 0)

)

buy 5+ yr=

SUMX(

    Data,

    IF([Count] > 60, 1, 0)

)

Personally i don't think these are hugley heavy calculations. But would be good to understand whats causing the issue

[Syk]

If Data is your table, every measure has to look at the entire table everytime it reevalutes. You can filter down your data first instead of using an if statement.

buy <1yr=
SUMX(
    FILTER(Data, [Count] > 0 && [Count] < 12),
    1
)

[chonchar]

I just learned about this really cool extension called Bravo.bi. It is an external tool that will idenfity values, columns, data, etc in your model that are not being used so you can delete unused information to optimize your performance. 

You can find it here: https://bravo.bi/ 

Hope this is of some value. 

[foodd]

Bravo.io is helpful, thank you for sharing.   A more capable external tool is Measure Killer | Brunner BI that focuses upon both the Model and Report for analysis of columns and measures.  As an external tool, it is Amazing.  The newest and most awesome feature is Measure Killer Analytics which is in Preview.  

Key features:

• Removing unused measures
• Removing unused columns
• Comprehensiv report about Power BI artifacts (measure, columns, tables) where they are used etc.
• Analyzing a MS Power BI tabular model
• Cleaning shared datasets offline or via XMLA
• Analyzing shared datasets
• Cleaning Power BI tenant
• Analyzing thin reports
• Analyzing live reports

Release Notes for 08/14/2023
Version 1.1.0
​
[FEATURES]
-All Power BI and Paginated reports in the Service can now be analyzed
-New dark mode theme
-Release notes displayed in MK
-Users can now copy the rows, details, and the entire table directly from the main window.
-Analytics section with expanded artifact hierarchy in calculations.
​
[BUG FIXES]
-Small fixes in shared dataset on local machine mode
-Empty layout issue for older reports
-Minor UI bug fixes

What does Measure Killer detect?

• Visuals - including filters applied to visuals, on pages or the whole report.

• Artifacts only used in custom visuals (see compatibility matrix below for details)

• Any kind of measure or relationship

• Columns used exclusively in Power Query, e.g. in joins, appends, references etc.

• Calculated columns

• Calculated tables

• Conditional formatting

What does not work?

• If you copy whole reports and then only do some minor changes, this can lead to false references.

• If you create new queries from existing queries in Power Query it is possible that MK will reference columns (e.g. selected columns / removing columns) that have already been removed in the existing query.

• If a query B (child) references a query A (parent) via e.g. an append, join etc. and those queries have the same column names. If we now have an M reference in one of these columns in query B it will wrongly flag the same column in query A as used.

• DAX expressions like COUNTROWS that only reference a table are not considered unless there is a column or measure referenced as well.

• Several runs may be necessary to delete all unused columns and measures. (There can be artifacts, that are only referenced in unused measures or columns but are not actually used anywhere in the report.)

• Default titles and subtitles (created by Power BI / default) will not be recognized. If you change the titles in any way they will be detected though.

• Restrictive sensitivity labels my produce errors.