Improve Performance of this MAX and SELECTEDVALUE Formula

[Russell-PBI]

Hi everyone,

Does anyone know a better way to write this DAX please?:

CALCULATE(
     MAX('Fact National Rate'[AchievementRatePct])
,    FILTER(
          ALL('Dim National Rate')
     ,    'Dim National Rate'[Age Band] = SELECTEDVALUE('Dim National Rate'[Age Band], "All Ages")
          && 'Dim National Rate'[Apprenticeship Level] = SELECTEDVALUE('Dim National Rate'[Apprenticeship Level], "All Apprenticeship Levels")
          && 'Dim National Rate'[Apprenticeship Type] = SELECTEDVALUE('Dim National Rate'[Apprenticeship Type], "All Apprenticeship Types")
          && 'Dim National Rate'[Provision Type] = SELECTEDVALUE('Dim National Rate'[Provision Type], BLANK())
          && 'Dim National Rate'[Delivery Local Authority District] = SELECTEDVALUE('Dim National Rate'[Delivery Local Authority District], "All Delivery Local Authority Districts")
          && 'Dim National Rate'[Delivery Local Enterprise Partnership] = SELECTEDVALUE('Dim National Rate'[Delivery Local Enterprise Partnership], "All Delivery Local Enterprise Partnerships")
          && 'Dim National Rate'[Delivery Region] = SELECTEDVALUE('Dim National Rate'[Delivery Region], "All Delivery Regions")
          && 'Dim National Rate'[Ethnicity] = SELECTEDVALUE('Dim National Rate'[Ethnicity], "All Ethnicities")
          && 'Dim National Rate'[Framework Code] = SELECTEDVALUE('Dim National Rate'[Framework Code], "All Framework Codes")
          && 'Dim National Rate'[Framework Title] = SELECTEDVALUE('Dim National Rate'[Framework Title], "All Framework Titles")
          && 'Dim National Rate'[GCSE Type] = SELECTEDVALUE('Dim National Rate'[GCSE Type], "All GCSE Types")
          && 'Dim National Rate'[Gender] = SELECTEDVALUE('Dim National Rate'[Gender], "All Genders")
          && 'Dim National Rate'[Learner Local Authority District] = SELECTEDVALUE('Dim National Rate'[Learner Local Authority District], "All Learner Local Authority Districts")
          && 'Dim National Rate'[Learner Local Enterprise Partnership] = SELECTEDVALUE('Dim National Rate'[Learner Local Enterprise Partnership], "All Learner Local Enterprise Partnerships")
          && 'Dim National Rate'[Learner Region] = SELECTEDVALUE('Dim National Rate'[Learner Region], "All Learner Regions")
          && 'Dim National Rate'[Learning Difficulty/Disability] = SELECTEDVALUE('Dim National Rate'[Learning Difficulty/Disability], "All Learning Difficulties and Disabilities")
          && 'Dim National Rate'[Level] = SELECTEDVALUE('Dim National Rate'[Level], "All Levels")
          && 'Dim National Rate'[Measure] = SELECTEDVALUE('Dim National Rate'[Measure], BLANK())
          && 'Dim National Rate'[Qualification Code] = SELECTEDVALUE('Dim National Rate'[Qualification Code], "All Qualification Codes")
          && 'Dim National Rate'[Qualification Level] = SELECTEDVALUE('Dim National Rate'[Qualification Level], "All Qualification Levels")
          && 'Dim National Rate'[Qualification Title] = SELECTEDVALUE('Dim National Rate'[Qualification Title], "All Qualification Titles")
          && 'Dim National Rate'[Qualification Type] = SELECTEDVALUE('Dim National Rate'[Qualification Type], "All Qualification Types")
          && 'Dim National Rate'[Sector Subject Area Tier 1] = SELECTEDVALUE('Dim National Rate'[Sector Subject Area Tier 1], "All Sector Subject Areas Tier 1")
          && 'Dim National Rate'[Sector Subject Area Tier 2] = SELECTEDVALUE('Dim National Rate'[Sector Subject Area Tier 2], "All Sector Subject Areas Tier 2")
     )
)

Sample data and PBI file: https://drive.google.com/file/d/19BX-kutAuS_prXo6_5yLgAv2OWMQUN5-/view?usp=sharing

Example output:

Example output

For background, the data is from the gov.uk's National Achievement Rate tables, which are statistics grouped by several sets of attributes (e.g., Age Band and Gender; Age Band and Level, etc.). The sets cannot be combined, so I've unioned all the data together into two tables (Dim National Rate and Fact National Rate) with the idea being that the DAX will handle pulling out the correct statistic based on the attributes used; whether it's at a value, sub-total or total level. The "All..." rows in the table are used where no single value is selected (i.e., on sub-total and total rows).

This all works in theory, but it's too slow in practice, at least using the method above. I've also tried splitting the filter into one expression per attribute, but a single filter using the && operator seems to return slightly quicker (at least that's what DAX Studio reports).

Thanks for any help.

[Anonymous]

Hi @Russell-PBI ,

I don't quite see the point of your doing this.  Please try to understanding functions for parent-child hierarchies in DAX.

More details: Understanding functions for parent-child hierarchies in DAX 

I have also found a similar post, please refer to it to see if it helps you.

Optimizing a measure 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Russell-PBI]

Thanks, @Anonymous. I've read through the 'Understanding functions for parent-child hierarchies in DAX' article, but do not know how I can apply it to the problem.

Using a small example, when trying to create a parent-child hierarchy, there would be rows with two or more parents dependent on the selection and position of attributes in a visual (e.g., if Age Band is above Level, then the ParentID is 2; if Level is above Age Band, then the ParentID is 3):

ID	ParentID	Age Band	Level
1	2 or 3	16-18	1
2	4	16-18	All Levels
3	4	All Ages	1
4	-	All Ages	All Levels

If there is a way to resolve this to determine the correct parent in a given position, how would I then use this information in a measure? The parent-child hierarchy functions are new to me, so any help you can provide to point me in the right direction would be appreciated. Could you use something like PATHLENGTH in a measure as a filter element?

Ignoring for a moment what solution I am currently working with/trying to improve and instead tackling "the point"—the problem I am trying to solve is how to get a percentage value (cannot be summed) from a dataset that includes subtotals for various combinations of attributes (as per the table above).

I have also read through the similar post you have linked, but am unable to see any correlation between the two problems.

[ValtteriN]

Hi,

Instead of using SELECTEDVALUE you can replace it with MAX. This usually improves performance a bit. In addition, I would consider creating a filter table with "enter data" then using INTERSECT to apply all the filters in your example. 

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Russell-PBI]

Thanks, ValtteriN. How would I swap in MAX instead please?

I'll also see if I can I can figure out how to use a filter table. Any additional advice on that would be appreciated.

I've added sample data and a PBI file to my original post for clarification.

[amitchandak]

@Russell-PBI , I am not sure the value add happing by comparing

'Dim National Rate'[Apprenticeship Type] =SELECTEDVALUE of this column, it will only give one value even when multiple are selected.

to me, this is not needed(so many of them). Unless there no other option

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Russell-PBI]

Thanks, amitchandak. I've added the sample data and PBI file to my original post.