cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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 following format:

ttopal_1-1605740305122.png

and

ttopal_2-1605740344324.png

To start simply, I add a summary table where I can filter based on region and then filter Cities based on sum of total sales. This gives me either TopN or BottomN options. For it to work i remove the interaction from city filter to region filter and summary table.  Here is what it looks like:ttopal_0-1605740245412.png

What I would like to do is to remove the region filter and get that information from the city filter.

I tried doing this by creating a measure to get the region from city selection.

Region = IF ( HASONEVALUE ( Table[Cities] ), SELECTEDVALUE ( Table[Region] ), "NSW" ) 

 

But to use the measure in any visual either I need to keep the interaction or it wont return anything. So it doesnt make any difference.

 

Appreciate any help.

 

 

1 ACCEPTED SOLUTION
Frequent Visitor

@daxer

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]
    )

 

View solution in original post

4 REPLIES 4
Frequent Visitor

@daxer

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]
    )

 

View solution in original post

Frequent Visitor

Thank you both @daxer 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

 

 

Solution Sage
Solution Sage

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.

Super User II
Super User II

Hi @ttopal -

 

Have you tried putting Region as a calculated column onto the Sales table (presumes a 1-to-many relationship from Table to Sales on "City"?

 

Region = RELATED(Table[Region])

 

If not, please share a pbix with sample data and the visuals you have already built.

 

Hope this helps

David

 




Proud to be a Super User!




Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.