Find first distinct value based on condition

[Cyberguy23]

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!

[LivioLanzo · Accepted solution]

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"
)

[LivioLanzo · Accepted solution]

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"
)

[Cyberguy23]

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

[LivioLanzo · Accepted solution]

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

 yes, it should be: 

[Cyberguy23]

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

[Ravinder1]

i want below result 

ID	Status	Result
123	Fullypaid	Fullypaid
123	1	Fullypaid
123	2	Fullypaid
123	3	Fullypaid
124	Fullypaid	Fullypaid
124	Fullypaid	Fullypaid
124	Fullypaid	Fullypaid
125	1	Non Fullypaid
125	2	Non Fullypaid
125	3	Non Fullypaid
126	1	Fullypaid
126	2	Fullypaid
126	Fullypaid	Fullypaid

[Ashish_Mathur]

Hi,

Write this calculated column formula

Column = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[ID]=EARLIER(Data[ID])&&Data[Status]="Fullypaid"))>0,"Fully paid","Not fully paid")

Hope this helps.

[LivioLanzo]

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

 

[Cyberguy23]

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
)

[LivioLanzo]

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

[Cyberguy23]

You are geniuos! Thankyou for your help. Both formulas worked. 

[Cyberguy23]

Livio, if you could help me with the last new column please. 

I need a column "FirstPeriodM1All" that is populated with a period number where M1 was first found per customer. The sample data will look like below.  The logic is similar that is in the "FirstPeriodM1" with the exception that all customer records are now populated with a corresponding period number (whole Number)

Customer	Method	Period	FirstPeriodM1	M1Customer	Combined Method	FirstPeriodM1All
C1	M2	1		X	M1 & M2 & M3	2
C1	M1	2	X	X	M1 & M2 & M3	2
C1	M3	2		X	M1 & M2 & M3	2
C1	M1	3		X	M1 & M2 & M3	2
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	2
C3	M3	3		X	M1 &M3 & M4	2
C3	M4	4		X	M1 &M3 & M4	2

[LivioLanzo]

Hello @Cyberguy23

You just need to leverage the first formula I posed, and use only this part:

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

[Cyberguy23]

Hi, this is great. Exactly what I need. Thanks for all your help. 

[Cyberguy23]

This is great, thank you!

[Cyberguy23]

Thank you, it worked. 

[Anonymous]

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