Forum Discussion
Dynamically generating a table using OR filters, then aggregating it in a measure
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.
travelsandbooks , Can you share the measure try summarize or values with countrows
like
Countrows(summarize(filter( Table, <Condition> ) , Table[Column]) )
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?
You cannot materialize a calculated table from a measure.
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?
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.