Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Maybe there is an easy fix to this problem, that i just havent been able to figure out.
I need to count order numbers on a specific date, but i only want to count the order number once, and it has to be the first time it appears. So if order number 213, have date 31/01/2022, and there is another entry on order number 213, on date 24/02/2022. I only want it to be counted the first time.
Hope it make sense, and there is a fix to it 🙂
Solved! Go to Solution.
I got myself reading documentation and found the solution!
FIRST VALUE ACCEPTED =
CALCULATE(
MINX( FILTER(Table, [Status] = "ACCEPTED"),Table[Date]),
ALLEXCEPT(Table,Table[Id])
)
Hi @Wengel ,
I created some data:
Here are the steps you can follow:
1. Create measure.
Flag =
IF(
MAX('Table'[Date])=MINX(FILTER(ALL('Table'),'Table'[ID]=MAX('Table'[ID])),[Date]),1,0)
2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Awesome solution!
I got the same problem in here and this solutions tends to solve at least 85% of the situation..
But i have another doubt:
In this dax function created to find the min(value), is there anyway to get the min(value) that has another condition?
For example:
ID | Date | Status
1--|10/01| ERROR
2--|11/01| ACCEPTED
3--|13/01| ACCEPTED
4--|18/01| ERROR
If i apply the query itself, it will return me the ID 1, since it has the min value, but instead, i would like to get the ID 2, since it is the min value accepted..
This question makes any sense? Sorry for any inconvenience 😛
I got myself reading documentation and found the solution!
FIRST VALUE ACCEPTED =
CALCULATE(
MINX( FILTER(Table, [Status] = "ACCEPTED"),Table[Date]),
ALLEXCEPT(Table,Table[Id])
)
You could create a calculated column to store the first order date and then link that to your Date table instead of, or as well as, linking the existing order date column.
The new column could be something like
First order date = CALCULATE( MIN('Sales'[Order date]), ALLEXCEPT('Sales', 'Sales'[Order ID]) )
and your number of orders measure would be either a simple DISTINCTCOUNT or a DISTINCTCOUNT with USERELATIONSHIP if you have multiple relationships from Date to Sales.
User | Count |
---|---|
65 | |
46 | |
20 | |
18 | |
15 |
User | Count |
---|---|
120 | |
41 | |
40 | |
28 | |
23 |