Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
C-F-G
Frequent Visitor

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

1 ACCEPTED SOLUTION
bhanu_gautam
Super User
Super User

@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])
)
)
)
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






View solution in original post

2 REPLIES 2
C-F-G
Frequent Visitor

Thank you for for advise and guidance. I was able to develop my solution.

bhanu_gautam
Super User
Super User

@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])
)
)
)
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.