Filter by matching criteria

[JamesGordon]

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.

[Anonymous]

// 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"
    )

[PaulOlding · Accepted solution]

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

[AlB]

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.