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
DCELL
Helper III
Helper III

Filter matrix for negative values

Hello,

 

I want to apply a measure filter to a matrix visual after the calculation has taken place instead of before.

In the screenshot below I only want to see the information for Part number DEF.

Current:

DCELL_0-1648587596044.png

Desired:

DCELL_4-1648588875251.png

 

Because of the extra details at the row level, shown below -

DCELL_2-1648588375098.png

 - applying a simple '<0' filter will not give result I'm looking for, because it will filter for <0 at the lowest level of the row hierarchy and then calculate, instead of filtering only after the calculation is complete.

 

A note, just in case it matters: when the matrix is collapsed, it displays the evaluation for "PJQOH", which is a running total of the rows above PJQOH (PJQOH means 'projected quantity on hand'). This custom display is achieved using IF( HASONEVALUE ( <column> ) ).

I hope this is enough information. Other answers have made me understand what is going wrong, but I've not been able to apply their solutions to this situation.

 

Link to file: https://drive.google.com/file/d/1JzT1Cwqh5anJ_A6dvVuWdGGpz73qj3Mt/view?usp=sharing

1 ACCEPTED SOLUTION

Hi @DCELL ,

 

You can try the following formula:

 

Filter_Consolidate = 
VAR temp_table =
    CALCULATETABLE (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE ( DemandSupply, DemandSupply[Part], Cal[Fiscal Year], Cal[rDate] ),
                "_Consolidated", [Consolidated Planning Data]
            ),
            [_Consolidated] < 0
        ),
        ALL ( Cal ),
        ALLEXCEPT ( DemandSupply, DemandSupply[Part] )
    )
RETURN
    COUNTROWS ( temp_table )

 

 

Has you can see below is only presenting one row:

MFelix_0-1648843810487.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

7 REPLIES 7
PaulDBrown
Community Champion
Community Champion

Try:

filter < 0 =

COUNTROWS(
CALCULATETABLE(VALUES(Table[Part]), FILTER(VALUES(Table[Part]), [your measure] < 0)))

Add this measure to the filter pane and set the value to = 1





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi @PaulDBrown ,

This does not work either.

I think I need to share the file, here is a link:

https://drive.google.com/file/d/1JzT1Cwqh5anJ_A6dvVuWdGGpz73qj3Mt/view?usp=sharing

MFelix
Super User
Super User

Hi @DCELL ,

 

On the filter pane on the matrix select the measure and then select all the values below 0. Should return correct value has you need.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix ,

This is what I was trying to explain with

"applying a simple '<0' filter will not give result I'm looking for, because it will filter for <0 at the lowest level of the row hierarchy and then calculate, instead of filtering only after the calculation is complete."

 

This is the result when filtering the measure for < 0 in the filter pane:

DCELL_0-1648824416875.png

The default behavior, as I understand it, is:

1) Filter

2) Evaluate

 

But I need

1) Evaluate

2) Filter the result of the evaluation for <0 at the top level of the row hierarchy

 

This is what I'm looking for:

DCELL_6-1648825275680.png

 

 

Hi  @DCELL ,

 

To understand you want to have the lines that have a negative value in any column is that it?

Because looking at the values you are presenting how do I know that the DEF should be present is because those two values are negative?

 

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix,
In simple terms, I need Power BI to "Show me parts that will have a projected quantity on hand less than zero at any point in time."

I edited my original post and pasted a link at the end.

Thanks!!

Hi @DCELL ,

 

You can try the following formula:

 

Filter_Consolidate = 
VAR temp_table =
    CALCULATETABLE (
        FILTER (
            ADDCOLUMNS (
                SUMMARIZE ( DemandSupply, DemandSupply[Part], Cal[Fiscal Year], Cal[rDate] ),
                "_Consolidated", [Consolidated Planning Data]
            ),
            [_Consolidated] < 0
        ),
        ALL ( Cal ),
        ALLEXCEPT ( DemandSupply, DemandSupply[Part] )
    )
RETURN
    COUNTROWS ( temp_table )

 

 

Has you can see below is only presenting one row:

MFelix_0-1648843810487.png

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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.