Creating new table: Too many rows error

[cathoms]

Hi. I'm trying to create a new table for use in a dynamic legend slicer and am getting the following error: "<pi>The resultset of a query to external data source has exceeded the maximums allowed size of '1000000' rows.</pi>".

I don't understand why this is happening. Shouldn't the resulting table contain only as many rows as categories I am using? Below is a screenshot showing all 24 of the fields I want in my LegendCategories table. Text highlighted in yellow are table names in the data model and items outlined in red are the field/column names.

And here is the DAX for the table with error message:

For ease of reading, here is the DAX code itself:

LegendCategories = 
UNION(
    SELECTCOLUMNS(
        PatientDim,
        "Category", PatientDim[RaceCategory1],
        "Field", "Race Category"
    ),
    SELECTCOLUMNS(
        PatientDim,
        "Category", PatientDim[RaceCategory2],
        "Field", "Race"
    ),
    SELECTCOLUMNS(
        PatientDim,
        "Category", PatientDim[Sex],
        "Field", "Sex"
    ),
    SELECTCOLUMNS(
        CovidVaccinationStatusFact,
        "Category", CovidVaccinationStatusFact[AgeGroup 1],
        "Field", "Age Group"
    ),
    SELECTCOLUMNS(
        CovidVaccinationStatusFact,
        "Category", CovidVaccinationStatusFact[CovidVaccinated],
        "Field", "Vaccination Status"
    ),
    SELECTCOLUMNS(
        CovidVaccinationStatusFact,
        "Category", CovidVaccinationStatusFact[Booster],
        "Field", "Booster Status"
    )
)

My expected output:

Category	Field
Female	Sex
Male	Sex
Other	Sex
BIPOC	Race Category
Unknown	Race Category
White	Race Category
Not Vaccinated	Vaccination Status
Vaccinated	Vaccination Status
Booster	Booster Status
No Booster	Booster Status

etc.

Am I doing something wrong or just misunderstanding how this works?

[cathoms]

Oh, great idea! Works perfectly, thanks!

[SpartaBI]

@cathoms my pleasure 🙂
Hey, check out my showcase report - got some high level stuff there. Sure you will find there a lot of cool ideas. 
https://community.powerbi.com/t5/Data-Stories-Gallery/SpartaBI-Feat-Contoso-100K/td-p/2449543
Give it a thumbs up over there if you liked it 🙂

[SpartaBI · Accepted solution]

@cathoms you are using DQ and the intermediate result exceeding the 1M rows.
I guess patientdim has a lot of rows.
Maybe try to change the code to:

LegendCategories =
UNION (
    SELECTCOLUMNS (
        VALUES ( PatientDim[RaceCategory1] ),
        "Category", PatientDim[RaceCategory1],
        "Field", "Race Category"
    ),
    SELECTCOLUMNS (
        VALUES ( PatientDim[RaceCategory2] ),
        "Category", PatientDim[RaceCategory2],
        "Field", "Race"
    ),
    SELECTCOLUMNS (
        VALUES ( PatientDim[Sex] ),
        "Category", PatientDim[Sex],
        "Field", "Sex"
    ),
    SELECTCOLUMNS (
        VALUES ( CovidVaccinationStatusFact[AgeGroup 1] ),
        "Category", CovidVaccinationStatusFact[AgeGroup 1],
        "Field", "Age Group"
    ),
    SELECTCOLUMNS (
        VALUES ( CovidVaccinationStatusFact[CovidVaccinated] ),
        "Category", CovidVaccinationStatusFact[CovidVaccinated],
        "Field", "Vaccination Status"
    ),
    SELECTCOLUMNS (
        VALUES ( CovidVaccinationStatusFact[Booster] ),
        "Category", CovidVaccinationStatusFact[Booster],
        "Field", "Booster Status"
    )
)

      

Showcase Report – Contoso By SpartaBI