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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Filter table visual by a slicer

Hi,

 

I have this table visual, and I want to filter it based on the selected value in the slicer (a package slicer).

Example of the table and the table visual is:

 

NoPackageValue
1A12
2A13
3B20
4C15
5All Packages10
6All Packages5
7All Packages0
8B8

 

More precisely what I want is:

 

* I can only have Package A, B, and C in the slicer. That is, package list has come from another data table related to this data table.

* When Package A, B, or C is not selected, rows related to "All Packages" should be shown.

* When any Package A, B, or C is selected, rows related to that package should only be shown (and not "All Packages" at all!).

 

This is the package table I should have:

NoPackage
1A
2B
3C

 

How can I achieve this task? 

Please help

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@amitchandak 

Your equation in the file was really really helpful.

 

I finally managed to solve the problem using COUNT on "No." column instead of SUM.

Then used the measure in the filter section, and limited it to equal 1 only.

 

So, final equation:

 

Measure2 =

IF(ISFILTERED(package[package]), CALCULATE(COUNT('Fact'[No]), FILTER('Fact', 'Fact'[Package] IN VALUES(Package[Package]))), CALCULATE(COUNT('Fact'[No]), FILTER('Fact', 'Fact'[Package] in ROW("Package","All Packages"))))

View solution in original post

9 REPLIES 9
amitchandak
Super User
Super User

@Anonymous , if the tables are disconnected, best option in this case

 

measure =
var _max = if(isfiltered(package[package]), values(package[package]), ROW("package","All Packages"))
return
calculate(sum(Table[Value]),filter(Table, Table[package] in _max))

 

 

if those are connected
measure =
var _max = if(isfiltered(package[package]), values(package[package]), ROW("package","All Packages"))
return
calculate(sum(Table[Value]),filter(package, package[package] in _max), removefilters(package[package]))

Anonymous
Not applicable

Thanks.

But this is not the answer to my question.

I don't want to calculate SUM.

I want to filter the table visual.

@Anonymous , Measure has a filter, so if this is the only measure, the visual table will filter. Or all the measures need to follow this type of code

Anonymous
Not applicable

Thanks.

But I'm getting errors:

 

mah65_0-1612755154412.png

 

mah65_1-1612755221289.png

 

 

 

 

Mind you that column "Value" is a string column (not numeric). Sorry, but I don't understand what you are summing.

 

Thanks.

@Anonymous , can you share complete formula you created

Anonymous
Not applicable

My pbix file is very large with many columns and with different column names.

Can you please share the file you created the measures in?

Much appreciated.

@Anonymous , Please find the attached file after the signature.

 

Anonymous
Not applicable

@amitchandak 

 

Thanks a lot.

 

I checked the file. It works well when data "Value" is of type numeric. My data is actually of type String. ( I should have provided a better working example in my post)

 

Could you please provide a solution for such type of data?

Many thanks

Anonymous
Not applicable

@amitchandak 

Your equation in the file was really really helpful.

 

I finally managed to solve the problem using COUNT on "No." column instead of SUM.

Then used the measure in the filter section, and limited it to equal 1 only.

 

So, final equation:

 

Measure2 =

IF(ISFILTERED(package[package]), CALCULATE(COUNT('Fact'[No]), FILTER('Fact', 'Fact'[Package] IN VALUES(Package[Package]))), CALCULATE(COUNT('Fact'[No]), FILTER('Fact', 'Fact'[Package] in ROW("Package","All Packages"))))

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors