Forum Discussion

DCELL's avatar
DCELL
Resolver I
4 years ago
Solved

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...
Need Help
  • MFelix's avatar
    MFelix
    4 years ago

    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's avatar
MFelix
Super User
4 years ago

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.

DCELL's avatar
DCELL
Resolver I
4 years ago

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:

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:

 

 

  • MFelix's avatar
    MFelix
    Super User
    4 years ago

    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.

    • DCELL's avatar
      DCELL
      Resolver I
      4 years ago

      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!!

      • MFelix's avatar
        MFelix
        Super User
        4 years ago

        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: