cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Filter 0 from One Matrix Column Only

Hi,

Posted this issue before but still not resolved. I've got the example PBI to help now. (https://drive.google.com/file/d/194BFC7gDw49tSzkBzqz0CJ6MUmxx7H18/view?usp=sharing)

I have a Matrix table with Values which appear in 3 categories (Table example below):

When displaying this data in a Matrix with Columns as Category and Values as Value it can be seen below. What I would like however, is the Matrix to filter out the 0's that appear BUT only if Category C has a 0 and not if A or B has a 0.  I guess I need some sort of measure to sort this as Advanced Filtering does not work (as filtering 0 removes all instances of 0 from values). The measure I've tried is already in the file and I also tried a calc. column with similar logic.

Any help would be appreciated.

1 ACCEPTED SOLUTION
Super User

@TotunG Perhaps something like:

``````Complex Selector =
VAR __Calculation = CALCULATE( [Measure], [Category] = "C" )
VAR __Return = IF( __Calculation = 0, 0, 1 )
RETURN
__Return``````

Then use the FILTER pane to filter the visual for when this measure equals 1.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
2 REPLIES 2
Super User

@TotunG Perhaps something like:

``````Complex Selector =
VAR __Calculation = CALCULATE( [Measure], [Category] = "C" )
VAR __Return = IF( __Calculation = 0, 0, 1 )
RETURN
__Return``````

Then use the FILTER pane to filter the visual for when this measure equals 1.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Resolver I

Think that worked Greg! I definitely need to use variables more - they always help break down the issue...I tend to want to try nest everything haha.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors