Forum Discussion
Find first distinct value based on condition
I have a table with customers and their ordering method in each period. I need to be able to find a first occurence of concatenation of 'Customer' and 'Method' where method is M1. I have tried multiple DAX functions but can't seem to find the right one.
| Customer | Method | Period | FirstMethodM1 (Result) |
| C1 | M1 | 1 | X |
| C1 | M2 | 2 | |
| C1 | M1 | 3 | |
| C1 | M1 | 4 | |
| C2 | M2 | 1 | |
| C2 | M2 | 2 | |
| C2 | M1 | 3 | X |
| C2 | M1 | 4 | |
| C2 | M2 | 5 | |
| C2 | M3 | 6 | |
| C3 | M1 | 2 | X |
| C3 | M2 | 3 | |
| C3 | M3 | 4 | |
| C4 | M1 | 3 | X |
| C4 | M2 | 4 | |
| C4 | M3 | 5 |
Thank you!
Hello Anonymous
you should be able to di tlike this:
IF( CALCULATE( MIN( Table1[Period] ), ALLEXCEPT( Table1, Table1[Customer] ), Table1[Method] = "M1" ) = Table1[Period], "X" )IF( AND(
CALCULATE( MIN( Table1[Period] ), ALLEXCEPT( Table1, Table1[Customer] ), Table1[Method] = "M1" ) = Table1[Period],
Table1[Period] = "M1"
), "X" )yes, it should be:
16 Replies
Hello Anonymous
you should be able to di tlike this:
IF( CALCULATE( MIN( Table1[Period] ), ALLEXCEPT( Table1, Table1[Customer] ), Table1[Method] = "M1" ) = Table1[Period], "X" )Hi Folks,
I have same structure but unable to build the logic for the same.
In my case i need to show distinct value in table visual example
unique id signvalue variation
1 m1 01
1 m2 02
1 m1 00
2 m1 01
2 au 00
2 m2 01
3 m2 00
3 m1 00
So basially when for one unique id if sign value is m1 and m2 i need to always show m1 by default. if m1 not present then only need to show m2. also if one unique id has 3 variation then condition is to display all 3 variation.
result would be
1 m1 01
1 m2 02
1 m1 00
2 m1 01
2 m2 01
3 m1 00
I tested with a broader data set and it seems to work but not in all the cases. For example it picks up and marks as "X" some other method that happen in the same period. This case is also possible.
The combination C1+M3+2 should not be marked as "X". Hopefully it is just an additional filter that is required.
Customer Method Period FirstPeriodM1 C1 M2 1 C1 M1 2 X C1 M3 2 X C1 M1 3 IF( AND(
CALCULATE( MIN( Table1[Period] ), ALLEXCEPT( Table1, Table1[Customer] ), Table1[Method] = "M1" ) = Table1[Period],
Table1[Period] = "M1"
), "X" )yes, it should be: