Comparison with SIblings

[Anonymous]

Hi,

Need your help in solving which looks like a simple problem.

I have below dataset

City	Store	Revenue
London	L-Store 1	100
London	L-Store 2	150
Sydney	S-Store 1	190
Sydney	S-Store 2	75
Sydney	S-Store 3	90
Chicago	C-Store 1	300
Chicago	C-Store 2	500

Store will go in a Slicer . When a User selects a Store from the slicer, its peers within the same city along with the Revenue should appear in a table.

For Example if I select S-Store 2, output should be

S-Store 1	190
S-Store 2	75
S-Store 3	90

and if possible S-Store2 highlighted.

Thanks.

[Zubair_Muhammad]

@Anonymous

Try this technique

First create a calculated table..From Modelling Tab>>New Table

This will serve as store slicer

StoreSlicer =
VALUES ( Table1[Store ] )

Now create this MEASURE

Measure =
VAR StoreSelected =
    SELECTEDVALUE ( 'StoreSlicer'[Store ] )
RETURN
    CALCULATE ( MIN ( Table1[City] ), Table1[Store ] = StoreSelected )

Not put this MEASURE in VISUAL Filter and select ISNOTBLANK

[Zubair_Muhammad · Accepted solution]

@Anonymous

File attached as well

[Anonymous]

Thanks Zubair for your help.

Would it be possible to do without a calculated table, on the original table.

As every new table in the model would require the relationships to be build. 

[Zubair_Muhammad]

HI @Anonymous

I think it is not possible because a slicer from the same table will filter the values selected.

But may be there is a way I am not aware of.

Nevertheless, you do not need to establish relationship between calculated table and your datamodel.

In my example, i did not create any relationship

[Anonymous]

Hi Zubair,

I did notice that there was no relationship between tables.

However that Store filter is a global filter , filtering other visuals as well on the report. It wont be possible to add another Slicer coming from calculated table

Thankg again for your help.