Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ttopal
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
ttopal
Frequent Visitor

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

 

View solution in original post

4 REPLIES 4
ttopal
Frequent Visitor

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

 

ttopal
Frequent Visitor

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

 

 

Anonymous
Not applicable

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.

dedelman_clng
Community Champion
Community Champion

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors