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:

     

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: