Dynamically generating a table using OR filters, then aggregating it in a measure

[travelsandbooks]

I have a report where my stakeholders want to know the distinct count of a field based on if ANY of a given number of dimensions apply, rather than if they ALL apply which is default PowerBI filter behaviour.

I've worked out how to create a measure that I can apply to a card / table to get the numbers, and it works for a small test dataset. Info about that is here: https://community.fabric.microsoft.com/t5/Desktop/Using-OR-condition-between-gt-2-slicers-and-slicin...

However, my dataset is enormous, and if I run it on there, I get the error:

The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows

I thought about instead of generating it on the fly in the measure, I generate a table that dynamically populates based on the slicers, and then all I need to do in the measure is get the distinct count. Is this possible and how can I do it? Thank you!

[lbendlin]

Yes, but.  You cannot materialize a calculated table from a measure. You can only use a table variable as part of the measure calculation. The final output of the measure needs to be a scalar value (like the distinct count).

You need to use disconnected tables to feed your slicers, and then in your measure use TREATAS or equivalent to read the selected values from the slicers and implement your OR logic.

Note:  To implement OR logic on a single column you can use KEEPFILTERS.

[travelsandbooks]

I've given more info here: https://community.fabric.microsoft.com/t5/Desktop/Dynamically-generating-a-table-using-OR-filters-th... thank you 🙂 

[amitchandak]

@travelsandbooks , Can you share the measure try summarize or values with countrows

like

Countrows(summarize(filter( Table,  <Condition> ) , Table[Column]) )

[travelsandbooks]

Sorry for my slow reply. 

My data model's main table is called Table1 and it has columns individual_id, category_name, subject_name, and product_name. I have disconnected tables with the unique values of each of the '...name' columns.

To be clear I want the new table to populate with the number of individual_ids from Table1 that match at least one of the three '... name' filters. I'll then use a measure to get the distinct individual_ids, hopefully preventing that error.

I have active slicers for each of the '...name' disconnected tables in the report itself. I've been experimenting with DAX for the dynamic table, and I've got something like the following:

Magic table =

VAR SelectedCategories = VALUES(ORCategory[Category_name])
VAR SelectedProducts = VALUES(ORProduct[Product_name])
VAR SelectedSubjects = VALUES(ORSubject[Subject_name])

VAR CategoryFiltered =
CALCULATETABLE(
VALUES(Table1[individual_id]),
TREATAS(SelectedCategories, Table1[Category name]))

VAR ProductFiltered =
CALCULATETABLE(
VALUES(Table1[individual_id]),
TREATAS(SelectedProducts, Table1[Product_name]))

VAR SubjectFiltered =
CALCULATETABLE(
VALUES(Table1[individual_id]),
TREATAS(SelectedSubjects, Table1[Subject_name]))

RETURN UNION(
CategoryFiltered,
ProductFiltered,
SubjectFiltered)

This works without errors, but it brings in *everything* - it doesn't change depending on what the slicers are set as in the report. Can you help, please?

[lbendlin]

You cannot materialize a calculated table from a measure.

[travelsandbooks]

Oh, thank you! Now I realise why I was getting stuck 🙂 is there any way that I can do what I need to apart from having it in a measure? Do you have any suggestions for how I can do it with a data set as big as mine? 

[lbendlin · Accepted solution]

As soon as you want the result to be influenced by user input you can only use a measure. If your measure is not performing well enough then you need to work on improving it's performance. There is a 1M row limit for this.