How to count distinct order numbers without losing data

[Anonymous]

Hi everyone!

I have read a lot of other posts but I can not solve the problem I'm currently facing.

At the moment, I have multiple tables in my dataset imported in powerBI.

One of which displays all received orders, including: 1. Order number, 2. Order status (1;2;3;4), 3. Order date, 4. Sales area, 5. Product etc. 

Here is an easy version of the dataset:

It can be the case that an order includes multiple products. 

In that case, multiple rows are created which start with the same order number. 

Also, when an order is changed, it will create another row.

My goal is to create a table like this (there are not 6 orders, but only 3 distinct orders):

I'm looking for a DAX function which can enable me to count the distinct number of (last placed) orders, without deleting information from the table (as in another table, I want to still be able to see that Sweden ordered bread, eggs & cheese (not just Cheese).

Therefore, I don't think the power query function of 'delete dublicated rows' works for my problem. 

I have tried both the DISTINCT and COUNTDISTINCT functions, but I'm only getting errors or the total sum of orders (in the picture example, the total distinct orders would be 3).

This, however, doesn't enable me to spread the data over areas. 

I was thinking perhaps a solution exists by creating another column, which can display "1" when an order number is only present once (order 2 in the picture), or when it was entered last (if the order number is more common, like order 1 & 3 in the picture), and else "0". 

I'm looking forward to your advice!

Jeffrey 

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Can you post that sample data as text?

[ChrisMendoza · Accepted solution]

@Anonymous -

You can change to:

[Anonymous]

Thank you @ChrisMendoza

Sorry, completely forgot this was possible too, I was thinking way too difficult.

Solved my problem!

[amitchandak]

you tried distinctcount or countdistinct ?