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 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
Solved! Go to Solution.
@Anonymous -
You can change to:
Proud to be a Super User!
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?
@Anonymous -
You can change to:
Proud to be a Super User!
Thank you @ChrisMendoza
Sorry, completely forgot this was possible too, I was thinking way too difficult.
Solved my problem!
you tried distinctcount or countdistinct ?
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |