cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JamesGordon
Helper II
Helper II

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.

 

Power Bi Ques 24.jpg

1 ACCEPTED SOLUTION
PaulOlding
Solution Supplier
Solution Supplier

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

View solution in original post

3 REPLIES 3
daxer
Solution Sage
Solution Sage

// 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
Solution Supplier
Solution Supplier

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

View solution in original post

AlB
Super User III
Super User III

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.

 

SU18_powerbi_badge

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.

 

 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.