Dax code optimization

[Pk8524]

I have issues with one of the charts I have created. When uploaded to powerbi service it exceeds allowed memory. Can you have a look at this:

There are 15 categories. Each category has 10 measures (in total 150 measures!). Each line represents different category of sales. For each category of sales I calculate min, max, avg, point (value for primary project) and a line - between min and max.

First measure to put the results on certain y axis:

_01 RA Category A = 90

Two measures for AVG; Similar measures are created for MIN and MAX.

_01 RA Category A AVG = 
AVERAGEX(
    ALLSELECTED(Project[Project Name Appearing In Report]),
    CALCULATE(
        [Total Sales],
        'Cost Code'[Group Element] = "Category A"
    )
)

_01 RA Category A AVG PROGRESS = 
VAR avgVal = [_01 RA Category A AVG]
VAR axisVal = MAX('Cost Spread Bar'[Axis])
RETURN
IF(
    avgVal > 0 && axisVal = ROUND(avgVal, 0),
    [_01 RA Category A]
)

Point measure for primary project:

_01 RA Category A POINT = 
CALCULATE(
    [Total Sales Primary Project],
    'Cost Code'[Group Element] = "Category A"
)

_01 RA Category A POINT PROGRESS = 
VAR pointVal = [_01 RA Category A POINT]
VAR roundedAxis = ROUND(pointVal, 0)
VAR axisVal = MAX('Cost Spread Bar'[Axis])

RETURN
IF(
    axisVal > 0 && axisVal = roundedAxis,
    [_01 RA Category A]
)

Line between max and min:

_01 RA Category A PROGRESS = 
VAR minVal = [_01 RA Category A MIN]
VAR maxVal = [_01 RA Category A MAX]
VAR axisVal = MAX('Cost Spread Bar'[Axis])
VAR minBin = ROUND(minVal, 0)
VAR maxBin = ROUND(maxVal, 0)

RETURN
IF(
    minVal > 0 && maxVal > 0 && axisVal >= minBin && axisVal <= maxBin,
    [_01 RA Category A]
)

 Maybe I over complicated something. It's my third iteration over this and the code is simpler but still doesnt allow me to show this in powerbi service.

[v-menakakota]

Hi @Pk8524 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-menakakota]

Hi @Pk8524 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @Pk8524 ,

I hope the provided information is helpful. Thank you @lbendlin for providing possible solution. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you and Regards,
Menaka Kota.

[lbendlin]

How familiar are you with DAX Studio , Formula/Storage engine, and query plans?

[Pk8524]

i am familiar with those concepts. Nor particularly with dax studio. What you have on your mind?

[lbendlin · Accepted solution]

I would suggest you install DAX Studio, learn how to use it, and then use it to examine the query plans for your queries. Then you can decide how to refactor the queries to reduce cardinality, formula engine usage, RI violations etc.