Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
hi,
I need to use DAX calculate column function to remove the dupplicate value:
Input | Expected Output |
A, A, B | A, B |
B, B, C | B, C |
how to do this?
thanks
Solved! Go to Solution.
Hey @Iamnvt ,
in Power Pivot you are able to use the CALENDAR function as is described here:
https://www.sqlbi.com/articles/generating-a-series-of-numbers-in-dax/
Here is the DAX statement from above now using the function CALENDAR:
output calendar =
var _in = 'Table'[input]
var _inAsPath = SUBSTITUTE(_in , ", " , "|")
var _inPathLength = PATHLENGTH(_inAsPath)
var _T = DISTINCT(SELECTCOLUMNS(ADDCOLUMNS(SELECTCOLUMNS(CALENDAR(1 , _inPathLength), "_Value", INT(''[Date])), "@item", PATHITEM(_inAsPath , [_Value] , TEXT)) , "@@item", [@item]))
return
CONCATENATEX(_T , [@@item], ", ")
Regards,
Tom
Hey @Iamnvt ,
you can use this DAX to create a calculated column:
output =
var _in = 'Table'[input]
var _inAsPath = SUBSTITUTE(_in , ", " , "|")
var _inPathLength = PATHLENGTH(_inAsPath)
var _T = CONCATENATEX(DISTINCT(SELECTCOLUMNS(ADDCOLUMNS(GENERATESERIES(1 , _inPathLength) , "@PathItem" , PATHITEM(_inAsPath , [Value] , TEXT)) , "@@pahItme" , [@PathItem])), [@@pahItme] , ", ")
return
_T
This is how it looks like:
@TomMartens thanks for the answer
DAX in Power Pivot doesn't have GENERATESERIES function.
is there any other way around?
Hey @Iamnvt ,
in Power Pivot you are able to use the CALENDAR function as is described here:
https://www.sqlbi.com/articles/generating-a-series-of-numbers-in-dax/
Here is the DAX statement from above now using the function CALENDAR:
output calendar =
var _in = 'Table'[input]
var _inAsPath = SUBSTITUTE(_in , ", " , "|")
var _inPathLength = PATHLENGTH(_inAsPath)
var _T = DISTINCT(SELECTCOLUMNS(ADDCOLUMNS(SELECTCOLUMNS(CALENDAR(1 , _inPathLength), "_Value", INT(''[Date])), "@item", PATHITEM(_inAsPath , [_Value] , TEXT)) , "@@item", [@item]))
return
CONCATENATEX(_T , [@@item], ", ")
Regards,
Tom