Filter a Measure by the notselected value on filter

[luisccmm]

Hi,

Filtering a measure by the values selected on a filter panel is easy, but I need to produce the contrary in other measure. So when I select a value on the filter, I expect that in one measure the left value in the filter are activated as a filter in the measure.

For example.

Filter Variable has only 4 values: Supplier1, Supplier 2, Supplier 3, Supplier 4

Filter Variable name: Supplier.

Filter variable values selected: Supplier 1

Filter Variable values not selected: Supplier 2 & Supplier 3 & Supplier 4

Database looks like the following

Product   Supplier

Product1 Supplier 1

Product 1 Supplier 2

Product 1 Supplier 3

Product 2 Supplier 3

Product 2 Supplier 4

What I expect is if I filter Supplier 1, this function displays the total number of unique products.

Filter=  Supplier 1

count (distinct products) + count ( {<supplier=e(supplier)>} distinct product)   =    Count ({<supplier=>}distinct product)

[tresesco]

May be using e(), like:

count ({<Products =e(Products)>}distinct products)

[luisccmm]

@tresesco the  solution you provided seem almost working in my data, but not exactly. I may have gave a bad example so I have rewrite the post adding E () function solution you  provided.  The problem is that  is not working as expected, I think the problem could come form how the data is structured, I hope the new example I have rewrited is more clear.

[tresesco]

Sorry, I did not get it right yet. Could you explain the expected output (number) ?

[luisccmm]

Yess, using the following database.

Filter variable values selected: Supplier 3

Product   Supplier

Product1 Supplier 1

Product 1 Supplier 2

Product 1 Supplier 3

Product 2 Supplier 3

Product 2 Supplier 4

Product 3 Supplier 1

Product 3 Supplier 2

Product 4 Supplier 1

count (distinct products) + count ( {<supplier=e(supplier)>} distinct product)   =    Count ({<supplier=>}distinct product)

2                                                 +     2                                                                                              =   4