Forum Discussion

antlufc's avatar
antlufc
Frequent Visitor
3 years ago
Solved

Column Refers to column in same table

Hi All,

 

I have attached a Power Bi data tabel with the fileds i have to try and get to the expected result as per below. 

I would like a DAX column that finds the Transaction type that refers to the SubscriptionVersion 1 based upon the customer reference.  I would like this result to be in ever row in the new column expected result below:

 

https://1drv.ms/u/s!AivZWzcfJJzngR4sM0fFXflWaQAx?e=IIoRQk 

column
DAX
  • tamerj1's avatar
    tamerj1
    3 years ago

    HI antlufc 
    Please refer to attached sample file with the solution

    Required Result = 
VAR CurrentCustomerTable = 
    CALCULATETABLE ( First_Transaction_Type, ALLEXCEPT ( First_Transaction_Type, First_Transaction_Type[Customer Reference] ) )
VAR FirstVersion = MINX ( CurrentCustomerTable, First_Transaction_Type[SubscriptionVersion Number] )
RETURN
    MAXX (
        FILTER ( CurrentCustomerTable, First_Transaction_Type[SubscriptionVersion Number] = FirstVersion ),
        First_Transaction_Type[Transaction type]
    )

2 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    HI antlufc 
    Please refer to attached sample file with the solution

    Required Result = 
VAR CurrentCustomerTable = 
    CALCULATETABLE ( First_Transaction_Type, ALLEXCEPT ( First_Transaction_Type, First_Transaction_Type[Customer Reference] ) )
VAR FirstVersion = MINX ( CurrentCustomerTable, First_Transaction_Type[SubscriptionVersion Number] )
RETURN
    MAXX (
        FILTER ( CurrentCustomerTable, First_Transaction_Type[SubscriptionVersion Number] = FirstVersion ),
        First_Transaction_Type[Transaction type]
    )