cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Cyberguy23 Frequent Visitor
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. 

 

CustomerMethodPeriodFirstMethodM1 (Result)
C1M11X
C1M22 
C1M13 
C1M14 
C2M21 
C2M22 
C2M13X
C2M14 
C2M25 
C2M36 
C3M12X
C3M23 
C3M34 
C4M13X
C4M24 
C4M35 

 

 Thank you!

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User
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"
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Super User
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: 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

13 REPLIES 13
Highlighted
Super User
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"
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Cyberguy23 Frequent Visitor
Frequent Visitor

Re: Find first distinct value based on condition

Thank you, it worked. 

Cyberguy23 Frequent Visitor
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. 

 

CustomerMethod PeriodFirstPeriodM1
C1M21 
C1M12X
C1M32X
C1M13 

 

 

Super User
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: 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Cyberguy23 Frequent Visitor
Frequent Visitor

Re: Find first distinct value based on condition

This is great, thank you!

Cyberguy23 Frequent Visitor
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. 

 

CustomerMethod PeriodFirstPeriodM1M1CustomerCombined Method
C1M21 XM1 & M2 & M3
C1M12XXM1 & M2 & M3
C1M32 XM1 & M2 & M3
C1M13 XM1 & M2 & M3
C2M21  M1 & M2 & M3 & M4
C2M32  M1 & M2 & M3 & M4
C2M43  M1 & M2 & M3 & M4
C3M12XXM1 &M3 & M4
C3M33 XM1 &M3 & M4
C3M44 XM1 &M3 & M4
Cyberguy23 Frequent Visitor
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
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

 

Capture.PNG 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Super User
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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!