Filter the data on the Result View due to special request

[SantoshKumar]

Hi All,

I have a special request to filter on the result set and the data is as follows:

I have Area,Customer,ListType and Parent Table. Since i do not have an option to attach the pbi file, i am posting the data below.

ListType

Games1

Games2

International1

International2

International3

International4

Store1

Store2

Store3

Store4

Area

Area1

Area2

Area3

Customer

Customer1

Customer2

Customer3

ListType	ParentId	Area	Customer
Games1	9NBLGGH1H2	Area1	Customer1
Games2	9NBLGGH1H3	Area2	Customer2
International1	9NBLGGH1H4	Area3	Customer3
International2	9NBLGGH1H5	Area1	Customer1
International3	9NBLGGH1H6	Area2	Customer2
International4	9NBLGGH1H7	Area3	Customer3
Store1	9NBLGGH1H8	Area1	Customer1
Store2	9NBLGGH1H9	Area2	Customer2
Store3	9NBLGGH1H10	Area3	Customer3
Store4	9NBLGGH1H11	Area1	Customer1
Games1	9NBLGGH1H12	Area2	Customer2
Games2	9NBLGGH1H13	Area3	Customer3
International1	9NBLGGH1H14	Area1	Customer1
International2	9NBLGGH1H15	Area2	Customer2
International3	9NBLGGH1H16	Area3	Customer3
International4	9NBLGGH1H17	Area1	Customer1
Store1	9NBLGGH1H18	Area2	Customer2
Store2	9NBLGGH1H19	Area3	Customer3
Store3	9NBLGGH1H20	Area1	Customer1
Store4	9NBLGGH1H21	Area2	Customer2

Above view is working as expected due to relationships between the tables but i have a request not to show few values even the selection is made.
below is an example of my request,
When i have selected Area1 and Customer1, i am able to see all the ListType available for it. here i want to exclude only International4 when i have selected Area1,Customer1 in the selection.

What i tried from my end was:
1. I tried to use SelectedValues and tried to return the data. i faced with error as return value has to use some aggregate function which would provide only single value instead of List of values (ListType table).

Please let me know in case i can achieve my requirement in any other ways.

Regards

Santosh

[v-stephen-msft · Accepted solution]

Hi @SantoshKumar ,

You can filter out International4 in the Filters.

But you'll find that if you don't choose, International4 won't show up.

If you only want to exclude International4 when selecting Area1 and Customer1, you can create a measure.

Measure = 
IF (
    ISFILTERED ( Area[Area] ) && ISFILTERED ( Customer[Customer] ),
    IF (
        SELECTEDVALUE ( Area[Area] ) = "Area1"
            && SELECTEDVALUE ( 'Customer'[Customer] ) = "Customer1"
            && MAX ( 'ListType'[ListType] ) <> "International4",
        1,
        0
    ),
    1
)

Put the measure into the visual-level filters and set up show items when the value is 1.

The fields of the table visual and slicers are then created as follows. Because the field references in the measure formula, the source of the fields in the table visual and slicers cannot be easily changed.

When your slicer does not select a value or another value, International4 is not filtered out.

Best Regards,

Stephen Tao

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

[SantoshKumar]

@v-stephen-msft , thank you for taking time and providing inputs 
You solution is providing the solution to only one extent.
1. When i select Area1 and Customer1 then international4 is not showing up - Done
2. When any Customer is not selected then international4 is showing up - Done
3. when i select any other customer then table is showing as empty. - InComplete.
I want a measure so that when i select Area1 and Customer1 then international4 should be hide and for other selections, it should show other data as per tabe.
Ex: If add Internation4 for Area1 and for Customer2 and customer3 etc.

when i select Area1 and Customer2 then it should show international4 which at present not showing up.

Could you please provide any suggestion on it.
Regards

Santosh Kumar P