cancel
Showing results 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

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:

 No Package Value 1 A 12 2 A 13 3 B 20 4 C 15 5 All Packages 10 6 All Packages 5 7 All Packages 0 8 B 8

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:

 No Package 1 A 2 B 3 C

How can I achieve this task?

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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"))))
9 REPLIES 9
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.

Super User

@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:

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

Thanks.

Super User

@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.

Super User

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

Anonymous
Not applicable

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

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

Announcements

#### 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.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### 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
Top Kudoed Authors