I have a matrix visual in a Power BI report that finds the percentage of people in a quintile that are not completing a qualification which reports on three academic years.

I need to pull out the largest percentage from the most recent year’s data.

The underlying data includes both the School and College that each student studies in.

The code below works fine for all school and or all colleges in Power BI desktop and Power BI Service.

The report the visual is used in has optional filters on the page that can limit to specific schools and/or colleges, and the code also works when the data is filtered by school or college in Power BI desktop.

However, when used in Power BI service the card visual displays the following error:

Couldn’t load the data for this visual

MdxScript(Model) (122, 9) Calculation error in measure ‘Data'[KM MAX IMD NC %]: SummarizeColumns() and AddMissingItems() may not be used in this context.

I am rather perplexed as to why the code works in the desktop and not in the service.

Any pointers on how to get the code working in the service would be most welcome.

Thanks you in advance for any help,

David

    KM IMD MAX % = 

VAR queryYear = Data[KM Current Year]
VAR valueTable = 
    SUMMARIZECOLUMNS(
        Data[IMD Quintile],
        FILTER(
            Data,
            Data[Academic Year Name] = queryYear
        ),
        FILTER(
            Data, [IMD Quintile] <> BLANK()
        ),
        FILTER(
            Data,[Ethnicity] <> BLANK()
        ),
        "Fraction", CALCULATE(DISTINCTCOUNT(Data[Student Number]),FILTER(Data, [Completion] = "Not Completed")) / DISTINCTCOUNT(Data[Student Number])
    )
RETURN

    MAXX( SELECTCOLUMNS(valueTable,[Fraction]), [Fraction] )'''