Forum Discussion

Suyheng's avatar
Suyheng
Frequent Visitor
8 years ago
Solved

Generate Unique ID in Power Query (M Code) or Power Power Pivot (DAX Code)

Hi Everyone,

 

Could anyone help me to write M or Dax Code to generate Unique ID as shown below

 

 

Old IDNew IDItemUnique ID
 1ATN13
12ATN23
23ATN33

 

Many thanks for your help.

 

Regards,

Heng

Need Help
  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    just adjust the measure New like so

     

    New = 
var New = 
IF(HASONEVALUE('Table1'[Customer])
	,CALCULATE(
        FIRSTNONBLANK('Table1'[New ID], 1)
        ,ALLEXCEPT('Table1',Table1[Customer])
        ,'Table1'[Status] = "Active"
    )
	,BLANK()
)
return
IF(HASONEVALUE('Table1'[Customer])
	,IF(VALUES(Table1[New ID]) = New
		,New
		,BLANK()
	)
)

    and the measure Revenue total like so

     

    Revenue total = 
var New = 
IF(HASONEVALUE('Table1'[Customer])
	,CALCULATE(
        FIRSTNONBLANK('Table1'[New ID], 1)
        ,ALLEXCEPT('Table1',Table1[Customer])
        ,'Table1'[Status] = "Active"
    )
	,BLANK()
)
return
IF(HASONEVALUE('Table1'[Customer])
	,IF(VALUES(Table1[New ID]) = [New]
		,CALCULATE( 
			SUMX(VALUES('Table1'[Customer]),	
				CALCULATE(
					SUM(Table1[Revenue])
					,ALLEXCEPT('Table1',Table1[Customer])
				)
			)
		)
		,BLANK()
	)
	,CALCULATE( 
			SUMX(VALUES('Table1'[Customer]),	
				CALCULATE(
					SUM(Table1[Revenue])
					,ALLEXCEPT('Table1',Table1[Customer])
				)
			)
		)
)

    Then the output will look like this

    Regards

     

8 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    Hey,

     

    can you please describe the rule how the "Unique ID" has to be rerived, and also I do not understand what makes the ID unique if it repeats 3 times.

     

    Regards 

    • Suyheng's avatar
      Suyheng
      Frequent Visitor
      8 years ago

      Hi TomMartens,

       

      For example, customer buy house No1 but later they change to House No2 and later on they change to House No3 but the revenue already paid in house No1 and House No 2. Thus I want to sum all the revenue for House No3 which I need to have the same ID in order for me to sum up the revenue

       

      Input:

       

      Old IDNew IDCustomerItemStatusRevenueNew
       1CustomerAHouse No1Inactive50003
      12CustomerAHouse No2Inactive100003
      23CustomerAHouse No3Active200003

      Output: M or Dax code to show this result

       

      Old IDNew IDCustomerItemStatusRevenueNew
      23CustomerAHouse No3Active350003

       

      Many thanks for help

      • TomMartens's avatar
        TomMartens
        Super User
        8 years ago

        Hey,

         

        so what you are saying is this:

        • the column "New" gets its value from the "latest" (maximum) value of column "New ID"
        • create a measure "overall revenues" and just display the value if the latest "New ID" is selected

        Is my understaning of your requirement correct?

         

        Regards