Forum Discussion

JamesGordon's avatar
JamesGordon
Icon for Helper II rankHelper II
5 years ago
Solved

Filter by matching criteria

Hi all,

 

I am trying to find a way to create a slider/filter to show orders that can be invoiced once all the stock has arrived. Each order has a unique identifier in column “Order Number” but the order can be made up of multiple stock items. The criteria is that all items with the same Order Number need to have a “status” of S before they can be invoiced. Example of what I am trying to achieve below. Ideally I’d like to create a slicer with the option “Ready to Invoice” “Waiting on Goods”.

 

I have tried to create an IF DAX however it only looks at each row.

 

Does anyone have a suggestion on how I could create a DAX to achieve this? Example of what I am trying to achieve below.

 

Thank you in advance.

 

  • PaulOlding's avatar
    PaulOlding
    5 years ago

    You should consider adding the column in Power Query, especially if you have a large table.

    If you want to do it with a DAX calculated column this should do it.

     

    Order Status =
    VAR _OutOfStockItems =
    CALCULATE(COUNTROWS(Orders),
    ALLEXCEPT(Orders, Orders[Order Number]),
    Orders[Status] = ""
    )
    VAR _Result = IF(ISBLANK(_OutOfStockItems), "Ready to Invoice", "Waiting on Goods")
    RETURN
    _Result

3 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi JamesGordon 

    Create a calculated column:

    FilterCol_ =
VAR SCount_ =
    CALCULATE (
        COUNT ( Table1[OrderNumber] ),
        Table1[Status] = "S",
        ALLEXCEPT ( Table1, Table1[OrderNumber] )
    )
VAR orderCount_ =
    CALCULATE (
        COUNT ( Table1[OrderNumber] ),
        ALLEXCEPT ( Table1, Table1[OrderNumber] )
    )
RETURN
    IF ( SCount_ = orderCount_, "Ready to Invoice", "Waiting on Goods" )

    If tis does not work share the sample data in a format that be copied, instead of on a pic.

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

     

  • PaulOlding's avatar
    PaulOlding
    Icon for Solution Sage rankSolution Sage
    5 years ago

    You should consider adding the column in Power Query, especially if you have a large table.

    If you want to do it with a DAX calculated column this should do it.

     

    Order Status =
    VAR _OutOfStockItems =
    CALCULATE(COUNTROWS(Orders),
    ALLEXCEPT(Orders, Orders[Order Number]),
    Orders[Status] = ""
    )
    VAR _Result = IF(ISBLANK(_OutOfStockItems), "Ready to Invoice", "Waiting on Goods")
    RETURN
    _Result
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    // T is the table.

[Invoicing Status] = // calculated column
var OrderNo = T[Order Number]
var AllStatusesAreS =
    ISEMPTY(
        FILTER(
            T,
            T[Order Number] = OrderNo
            &&
            T[Status] <> "S"
        )
    )
return
    if( AllStatusesAreS,
        "Ready To Invoice",
        "Waiting on Goods"
    )