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

Thank you both Anonymous and dedelman_clng 

 

Looks like there is really no way to do this. I found many ways to filter down the region slicer but it needs me manually ticking the box.

 

This is a weird situation as almost any report I can think of is going to need to have how it compares feature. As in cities near me, customers in the same group, items in the same category etc. So basically it sounds like allowing a filter based on a slicer but one hierarchy level up. 

 

Shame it cant be done as the actual use case would inform not only region but level and function too. So it means I will have three additional slicers to get the relevant information which should come from one slicer naturally. 

 

Here is a link for pbix file if anyone wants to give it a go:

https://drive.google.com/file/d/1GthZYRDRs064sB9c2B5MAVUL-ikAjAQa/view?usp=sharing