How to apply a filter to same attribute on multiple table for a report?

[Typhoon74]

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.

[Typhoon74]

Hi @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 · Accepted solution]

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 result

On 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

[Typhoon74]

Hi @johnt75,
there are NO other measures in my data set. COST is just a column having cost vale in.
Is this solution working without additional measures?

[johnt75]

OK, in the code I posted above just replace both instances of [COST] with SUM( 'TableName'[COST] ) and then use this new measure on your visual instead of the COST column

[Typhoon74]

It is not working,
When using the Measure on my visual it is already showing a reduced value.
If I then select Archived the visual is showing blank and if I select Not archived nothing happens

[johnt75]

By default it will show a Not Archived value, that's why you're seeing no change when you choose Not Archived.

Can you check the values in the Archive columns on the 3 tables that have them. Are they text or integer, and if they are text is it possible that they would have trailing spaces ? The code I wrote is assuming that they are simple integers but if there are other values in there then it would need to be adapted to account for that

[Typhoon74]

All Archive values are integer values.

Please have a look at the PBIX file with all the changes  ExampleData Central Archive.pbix

[v-yanjiang-msft]

Hi @Typhoon74 ,

In your sample, if you want to calculate the cost when all the below attribute are archived, it will return nothing, it's correct.

Area 
 |_Customer 
   |_ Model 
     |_ Project 
       |_ Component 
         |_ Phase 
           |_ Tests 

For the TESTS table, when the ARCHIVE is 1, the PHASE ID are58,59,63,64,65,66,67,68,69,70,71,73,75,76,78,79,80

For the PHASES table, when the ARCHIVE is 1, the PHASE ID are 61 and 73.

For the two tables, TESTS and PHASES, when both tables only select archived as 1, only when the PHASE ID is 73, the corresponding COMPONENT ID is 54.

For the COMPONENTS table, when the ARCHIVE is 1, the COMPONENT ID is 55, not 54, so there's no overlapping value when the ARCHEVE of all these attribute is 1. 

So when you select Archived the visual is showing blank.

What do you want it to show, or do you have more requirements for the calculation?

Best Regards,
Community Support Team _ kalyj

[Typhoon74]

Hi @v-yanjiang-msft,
thanks for your feedback, my requirement was that I want to calculate the cost of all the archived when 1 is selected and all the cost of the non-archived when 0 is selected.

[v-yanjiang-msft]

Hi @Typhoon74 ,

As I mentioned before, when 1 is selected in all attributes, no cost will return, because the values all attributes filtered out are not overlapping.

I 'm not sure whether I understand your requirement correctly, just in your sample, please kindly let me know what's value of "the cost of all the archived when 1 is selected" as you think.

Best Regards,
Community Support Team _ kalyj

[Typhoon74]

You're right, I must have had a knot in my mind somehow. Sometimes you don't understand your own thoughts.

It is true, as you said, if I click 1 in Area, then there is no further data to be displayed.
The solution that @johnt75 is what I will use.

[johnt75]

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 result

It 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.