CODEWITHSUNDEEP
powerbidaxslicersdynamic-tables
travelsandbooks
travelsandbooks

Reputation: 333

Create a dynamic DAX table where at least one slicer applies

I have a report where my stakeholders want to know the distinct count of a individual_id based on if ANY slicers apply, rather than if ALL filters apply which is default PowerBI filter behaviour.

I've worked out how to do this using a measure that I can apply to a card / table to get the numbers, and it works for a small test dataset. However when I try and do it for my real dataset, it errors out because it's too much data. I would like to create a table that dynamically updates when slicers are updated; I can then create a measure that just gets the distinct count of that.

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. I've been experimenting with DAX 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?

Upvotes: 0

Views: 41

Answers (0)

Related Questions