Forum Discussion
Counting distinct over several columns
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
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])
)
)
)
)
2 Replies
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])
)
)
)
)Thank you for for advise and guidance. I was able to develop my solution.
