cancel
Showing results for
Did you mean:
Frequent Visitor

## 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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User

## Re: Find first distinct value based on condition

Hello @Cyberguy23

you should be able to di tlike this:

```IF(
CALCULATE(
MIN( Table1[Period] ),
ALLEXCEPT( Table1, Table1[Customer] ),
Table1[Method] = "M1"
) = Table1[Period],
"X"
)```

Proud to be a Datanaut!

Super User

## Re: Find first distinct value based on condition

```IF(
AND(      CALCULATE(
MIN( Table1[Period] ),
ALLEXCEPT( Table1, Table1[Customer] ),
Table1[Method] = "M1"
) = Table1[Period],      Table1[Period] = "M1"    ),
"X"
)```

yes, it should be:

Proud to be a Datanaut!

13 REPLIES 13
Highlighted
Super User

## Re: Find first distinct value based on condition

Hello @Cyberguy23

you should be able to di tlike this:

```IF(
CALCULATE(
MIN( Table1[Period] ),
ALLEXCEPT( Table1, Table1[Customer] ),
Table1[Method] = "M1"
) = Table1[Period],
"X"
)```

Proud to be a Datanaut!

Frequent Visitor

## Re: Find first distinct value based on condition

Thank you, it worked.

Frequent Visitor

## Re: Find first distinct value based on condition

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

Super User

## Re: Find first distinct value based on condition

```IF(
AND(      CALCULATE(
MIN( Table1[Period] ),
ALLEXCEPT( Table1, Table1[Customer] ),
Table1[Method] = "M1"
) = Table1[Period],      Table1[Period] = "M1"    ),
"X"
)```

yes, it should be:

Proud to be a Datanaut!

Frequent Visitor

## Re: Find first distinct value based on condition

This is great, thank you!

Frequent Visitor

## Re: Find first distinct value based on condition

Livio, thank you for your help. The initial questioned that I asked was answered by you in the previous email. But as I build more measure I need some more attributes. The "M1Customer" field should contain for customers that have ever ordered with method M1. See the table below.

As well as I need a "Combined Method" set for a customer that concatenates all "Method" values sorted alphabetically within a customer and displays the result in every row of a customer.

 Customer Method Period FirstPeriodM1 M1Customer Combined Method C1 M2 1 X M1 & M2 & M3 C1 M1 2 X X M1 & M2 & M3 C1 M3 2 X M1 & M2 & M3 C1 M1 3 X M1 & M2 & M3 C2 M2 1 M1 & M2 & M3 & M4 C2 M3 2 M1 & M2 & M3 & M4 C2 M4 3 M1 & M2 & M3 & M4 C3 M1 2 X X M1 &M3 & M4 C3 M3 3 X M1 &M3 & M4 C3 M4 4 X M1 &M3 & M4
Frequent Visitor

## Re: Find first distinct value based on condition

For the "Combined Method" I used the following formula which work for me but I also need to only get concatenation of distinct values.

```CombinedMethod =
CONCATENATEX (
FILTER (Table1, Table1[Customer] = EARLIER(Table1[Customer])),
Table1[Method],
", ",Table1[Method],ASC
)```

Super User

## Re: Find first distinct value based on condition

For the M1 column try this:

```M1 Customer =
IF(
NOT(
ISEMPTY(
CALCULATETABLE(
Data,
ALLEXCEPT( Data, Data[Customer] ),
Data[Method ] = "M1"
)
)
),
"X"
)
```

and for the other, try:

```Column =
VAR MethodsTable =
CALCULATETABLE ( VALUES ( Data[Method ] ), ALLEXCEPT ( Data, Data[Customer] ) )
VAR RankedMethods =
ADDCOLUMNS ( MethodsTable, "Value", RANKX ( MethodsTable, [Method ],, ASC ) )
VAR Test =
CONCATENATEX (
NATURALINNERJOIN (
RankedMethods,
GENERATESERIES ( 1, COUNTROWS ( MethodsTable ) )
),
[Method ],
" & "
)
RETURN
Test```

Proud to be a Datanaut!

Super User

## Re: Find first distinct value based on condition

Hello @Cyberguy23

For the M1 Column, try:

```M1 Customer =
IF(
NOT(
ISEMPTY(
CALCULATETABLE(
Data,
ALLEXCEPT( Data, Data[Customer] ),
Data[Method ] = "M1"
)
)
),
"X"
)```

And for the other:

```Column =
VAR MethodsTable =
CALCULATETABLE ( VALUES ( Data[Method ] ), ALLEXCEPT ( Data, Data[Customer] ) )
VAR RankedMethods =
ADDCOLUMNS ( MethodsTable, "Value", RANKX ( MethodsTable, [Method ],, ASC ) )
VAR Test =
CONCATENATEX (
NATURALINNERJOIN (
RankedMethods,
GENERATESERIES ( 1, COUNTROWS ( MethodsTable ) )
),
[Method ],
" & "
)
RETURN
Test```