Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
// 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"
)
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.
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. |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
73 | |
51 | |
45 | |
16 | |
12 |