Forum Discussion
Helper IHow to apply a filter to same attribute on multiple table for a report?
Here you can find an example file with data
I have the following situation in my data model. I hope I manage to describe it clearly.
The data tables each have their own archive attribute, which is cascaded down, based on the table relationships, when the upper level is put into the archive.
However, not in the form that the lower level is also set the attribute 0 or 1, but somehow simulated by a logic.
e.g. If project A is set to archive (1), then all components, phases, and tests are also shown as archived, but without also setting archive to (1) in the corresponding table.
Area
|_Customer
|_ Model
|_ Project A (1)
|_ Component
|_ Phase
|_ Tests
|_ Project B
|_ Component
|_ Phase
|_ Tests
|_ Component
|_ Phase
|_ Tests
|_ Project C
|_ Component
|_ Phase
|_ Tests
Now, in order for the user to include or exclude all archived entries, it is necessary to select archive 0 or 1 for each table, as I have prepared in the sample file.
However, I would like to provide users with a single button to filter all tables to archive 0 or 1.
Is there any way to do this using a single slicer, if so how can I solve this?
This is the table relation
Table Relation
And this is the report view
Report
Thanks beforehand for any idea.
Use the Enter Data functionality to create a table like
This new table does not need a relationship to any other tables, all you're going to do with it is use it on a slicer instead of all the slicers on individual columns which you currently have. You can delete all those other slicers.
Looking at the image you posted it looks like you already have defined a measure COST which you're using on the column chart. Assuming that is the case, you can define a new measure as
COST Filtered =
var chosenFilter = SELECTEDVALUE( 'Archive Selection'[Archived] )
var result = IF ( chosenFilter = "Archived",
CALCULATE( [COST],
TREATAS( { 1 }, 'AREAS'[Archive] ),
TREATAS( { 1 }, 'CUSTOMER'[Archive]),
TREATAS( { 1 }, 'MODELS'[Archive] )
),
CALCULATE( [COST],
TREATAS( { 0 }, 'AREAS'[Archive] ),
TREATAS( { 0 }, 'CUSTOMER'[Archive]),
TREATAS( { 0 }, 'MODELS'[Archive] )
)
)
return resultOn the column visual you could then replace the COST measure with the new COST Filtered measure and the values will change depending on the user selection on the Archived slicer
12 Replies
johnt75Super User
I'd set up a disconnected table to use on the slicer, then have your measure check the value of that slicer and depending on whether or not they've chosen "archived" then apply the filters within the slicer.
Assuming you already have measures defined to do the calculation, you could do something like the below
My Measure Filtered =
var chosenFilter = SELECTEDVALUE( 'Archive Selection'[Value] )
var result = IF ( chosenFilter = "Archived",
CALCULATE( [My Measure],
TREATAS( { 1 }, 'AREAS'[Archive] ),
TREATAS( { 1 }, 'CUSTOMER'[Archive]),
TREATAS( { 1 }, 'MODELS'[Archive] )
),
CALCULATE( [My Measure],
TREATAS( { 0 }, 'AREAS'[Archive] ),
TREATAS( { 0 }, 'CUSTOMER'[Archive]),
TREATAS( { 0 }, 'MODELS'[Archive] )
)
)
return resultIt looks from your model like filters on those 3 tables would propagate to everywhere else, but you can add more tables in to the TREATAS section as needed.
Typhoon74Hi johnt75 ,
thanks for your solution approach. I am not able to follow your thoughts.
There are no measures for the current Archive slicer, these slicers are directly connected to the table column.Can you give some more details on what disconnected table and My Measure I need to define?
Many thanks
I did create the following table
And for Measure, I created the following
- johnt75
Use the Enter Data functionality to create a table like
This new table does not need a relationship to any other tables, all you're going to do with it is use it on a slicer instead of all the slicers on individual columns which you currently have. You can delete all those other slicers.
Looking at the image you posted it looks like you already have defined a measure COST which you're using on the column chart. Assuming that is the case, you can define a new measure as
COST Filtered =
var chosenFilter = SELECTEDVALUE( 'Archive Selection'[Archived] )
var result = IF ( chosenFilter = "Archived",
CALCULATE( [COST],
TREATAS( { 1 }, 'AREAS'[Archive] ),
TREATAS( { 1 }, 'CUSTOMER'[Archive]),
TREATAS( { 1 }, 'MODELS'[Archive] )
),
CALCULATE( [COST],
TREATAS( { 0 }, 'AREAS'[Archive] ),
TREATAS( { 0 }, 'CUSTOMER'[Archive]),
TREATAS( { 0 }, 'MODELS'[Archive] )
)
)
return resultOn the column visual you could then replace the COST measure with the new COST Filtered measure and the values will change depending on the user selection on the Archived slicer