Exclude a selected value from a matrix from the entire dashboard

[Anonymous]

Hey folks, 

I have this ability that I am trying to get. 

I have a matrix with 2 Rows, 1 Column and "Cost" in values. 

What I am trying to achieve is the ability to select on any cost value in this matrix and exclude this value from certain visuals.

For example, when I select the "2500" from the matrix, the "2500" will be excluded from both, Visual 1 and Visual 2. Visual 1 would show "1400" and in Visual 2, Apple would be "250" only.

[v-alq-msft]

Hi, @Anonymous 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached at the end.

Table:

New Table:

New Table = DISTINCT('Table'[A])

There is no relationship between two tables:

You may create two measures as below.

Result = 
var _cost = SELECTEDVALUE('Table'[Cost])
var list = DISTINCT('Table'[Cost])
var _result = 
CALCULATE(
        SUM('Table'[Cost]),
        FILTER(
            ALL('Table'),
            NOT('Table'[Cost] in list)
        )
)
return
IF(
    ISBLANK(_result),
    SUM('Table'[Cost]),
    _result
)

Result2 = 
var listA = DISTINCT('Table'[A])
var list = DISTINCT('Table'[Cost])
var _tab = 
SUMMARIZE(
    'New Table',
    'New Table'[A],
    "Cost",
    var _a = [A]
    var _re = 
    CALCULATE(
            SUM('Table'[Cost]),
            FILTER(
                ALL('Table'),
                'Table'[A] = _a&&
                NOT('Table'[Cost] in list)
            )
    )
    return
    IF(
        ISFILTERED('Table'[A]),
        IF(        
            _a in listA,
            _re,
            CALCULATE(
                SUM('Table'[Cost]),
                FILTER(
                    ALL('Table'),
                    'Table'[A] = _a
                )
            )
        ),
        CALCULATE(
                SUM('Table'[Cost]),
                FILTER(
                    ALL('Table'),
                    'Table'[A] = _a
                )
        )
    )
)
return
SUMX(
     _tab,
     [Cost]
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

One more thing @v-alq-msft., I have applied 2 filters for Visual 1 in the filter pane. When I added this Result2 measure, the Visual 1 is showing the total number without taking into consideration the 2 applied filters. Do you know how can I solve this? I do not mind hard coding these 2 filters in the measure.

[v-alq-msft]

Hi, @Anonymous 

I wonder you apply 'visual level filter' or 'page level filter' or 'report level filter' to 'Visual 1'and what the filters are like. 'Visual 2'  uses 'A' column from 'New Table', which is a separated table. You are unable to use 'A' column from 'Visual 2' to filter 'Visual 1'. 

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-alq-msft I applied 2 Visual Level Filters. I will describe my problem in a more details:

I have 2 tables, "Table" and "Table Person". I have used your measure in "Visual 1 - Result2 Measure" and now its showing the summation of all "Cost" values. Originally I had 2 visual level filters applied on "Visual 1 Original" which are "Filter 1" = On and "Filter 2"=Yes. 

1. How can we add these 2 filters in the measure or the card?

2. I have other card visuals such as "Visual 3" and "Visual 4" that are showing the "Sales" and "Number of Items". How can we exclude the matrix selection from them? I want to see the impact on Sales when I exclude a certain cost from the matrix?

3. I have other stacked bar visuals and line and stacked column chart. When I added the measure to the column values in these visuals, it showed wrong values. 

I really appreciate your help in this as its a very complex task for myself and I am looking forward for your reply.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

You may try the following measures.

Result = 
var _cost = SELECTEDVALUE('Table'[Cost])
var list = DISTINCT('Table'[Cost])
var _result = 
CALCULATE(
        SUM('Table'[Cost]),
        FILTER(
            ALL('Table'),
            'Table'[Filter1] = "On"&&
            'Table'[Filter2] = "Yes"&&
            NOT('Table'[Cost] in list)
        )
)
return
IF(
    NOT(ISFILTERED('Table'[A])),
    CALCULATE(
        SUM('Table'[Cost]),
        FILTER(
            ALL('Table'),
            'Table'[Filter1] = "On"&&
            'Table'[Filter2] = "Yes"
        )
    ),
    _result
)

Result2 = 
var listA = DISTINCT('Table'[A])
var list = DISTINCT('Table'[Cost])
var _tab = 
SUMMARIZE(
    'New Table',
    'New Table'[A],
    "Cost",
    var _a = [A]
    var _re = 
    CALCULATE(
            SUM('Table'[Cost]),
            FILTER(
                ALL('Table'),
                'Table'[A] = _a&&
                NOT('Table'[Cost] in list)
            )
    )
    return
    IF(
        ISFILTERED('Table'[A]),
        IF(        
            _a in listA,
            _re,
            CALCULATE(
                SUM('Table'[Cost]),
                FILTER(
                    ALL('Table'),
                    'Table'[A] = _a
                )
            )
        ),
        CALCULATE(
                SUM('Table'[Cost]),
                FILTER(
                    ALL('Table'),
                    'Table'[A] = _a
                )
        )
    )
)
return
SUMX(
     _tab,
     [Cost]
)

Then you may try to use column 'A' from New Table(a calculated table) as 'Axis' and use 'Result2' as Value.

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hey @v-alq-msft, 

Thanks for your help. Any idea about the other cards, "Sales" and "Number of Items"?

[v-alq-msft]

Hi, @Anonymous 

A measure operates on aggregations of data defined by the current context. It depends on the specific context where you calculate the measure.

Best Regards

Allan

[Anonymous]

Thanks @v-alq-msft, for your solution. It worked for the cards but I am not sure how to do them for the other visuals.

1. I have other visuals such as "Stacked Bar Charts" and "Line and Stacked Column Chart". How can I use this measure for them?

2. I have other visual cards that are showing "Sales". How will I see the impact of excluding certain "Cost values" from the matrix on the "Sales" Card?

[Greg_Deckler]

Would need to understand the underlying data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Could potentially use HASONEVALUE.

[Anonymous]

This is the data.

 @Greg_Deckler 

 

[Greg_Deckler]

So you could create a measure like:

Visual1Measure =
IF(HASONEVALUE('Table'[Cost]),SUMX(ALL('Table'),[Cost]) - MAX('Table'[Cost]),SUMX('Table'[Cost]))

[Anonymous]

Thanks @Greg_Deckler, I think we are almost there.

On Visual 1, there are 2 filters applied. When I added the visual1measure to Viusal 1, it neglected the filters and showed me the number as if there are not filters applied. 

I have other stacked bars charts, will the measure work for them?

Thank you so much