Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Table with indirect filter

Hi all,   I am trying to build a custom visual where I can show how a filtered city compares to other cities in its region.  For simplicity I have created a sales table and a cities table in follo...
  • Anonymous's avatar
    Anonymous
    5 years ago

    Anonymous

    I have revisted this problem for a different report which I found an easy solution. In case someone has a similar question the dax formula is below. 

     

     

    neighbours =
VAR currentregion =
    SELECTEDVALUE ( Cities[Region] )
RETURN
    SUMX (
        CALCULATETABLE (
            FILTER ( Sales, Sales[Region] = currentregion ),
            ALL ( Cities[City] )
        ),
        Sales[Sale]
    )

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Here's what I think you should do. Leave all the slicers as they are. Hide the Region slicer on the canvas but use a measure to filter its selections based on the selections in City (use the Filter Pane, the filtering measure will return 1 when the selected city is in the region and 0 otherwise). Remove the interaction between your table and the City slicer but leave it between Region and the table. This should work the way you want.

 

I just realized that you don't have to filter the Region slicer. It'll be filtered automatically since the field comes from the same table as City.

 

[Edit] Sorry, I've checked this - it won't work. I can't see any other way but the way you have it. You have to have a Region slicer shown and you have to select the one option that appears when you select just one city from the City slicer. Sorry. You're a bit out of luck here.