Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi all,
I have an order table lived connected with a SSAS/Report server.
The table has columns with vehicle IDs (VINs, not distinct), with project name and with order status.
Order status can be either FINISHED or CANCELLED. Each combination may occur multiple times.
I want to count cancelled orders per project where no subsequent finished orders exists in the same project.
The first part with counting canncelled orders and unique VINs works fine. I show it in a table visual per project, so the count per project is not needed in the dax code.
"Cancelled no following order = CALCULATE(DISTINCTCOUNT(orders[VIN]),order[status] IN {"CANCELLED"})"
But I cannot figure out the second scenario when a VIN exists multiple times and all orders for this vin are cancelled (there is no finished order for this VIN in the same project). Obviously I cannot use distinctcount when 2 or more cancelled orders exists for the same VIN. How can I do that?
Can you please advise...
CFG
Solved! Go to Solution.
@C-F-G , Create a measure to count the distinct VINs where all orders are cancelled within the same project.
CancelledNoFollowingOrder =
CALCULATE(
DISTINCTCOUNT(orders[VIN]),
orders[status] = "CANCELLED",
NOT (
EXISTS (
FILTER (
orders,
orders[status] = "FINISHED" && orders[VIN] = EARLIER(orders[VIN]) && orders[project] = EARLIER(orders[project])
)
)
)
)
Proud to be a Super User! |
|
Thank you for for advise and guidance. I was able to develop my solution.
@C-F-G , Create a measure to count the distinct VINs where all orders are cancelled within the same project.
CancelledNoFollowingOrder =
CALCULATE(
DISTINCTCOUNT(orders[VIN]),
orders[status] = "CANCELLED",
NOT (
EXISTS (
FILTER (
orders,
orders[status] = "FINISHED" && orders[VIN] = EARLIER(orders[VIN]) && orders[project] = EARLIER(orders[project])
)
)
)
)
Proud to be a Super User! |
|
User | Count |
---|---|
121 | |
69 | |
66 | |
56 | |
52 |
User | Count |
---|---|
181 | |
85 | |
67 | |
61 | |
53 |