Unique ids from a string using DAX measure

[talkprem]

Hi All,

I have data in this manner

id	value
10023	123,456
10023	123,456,789
10023	456,789

which by using a DAX measure i need to show in tablix visual as below for the value column, please dont give reply for power query as that may not fit my purpose.
Looking for DAX measure approach

id	value
10023	123,456,789

[AntrikshSharma · Accepted solution]

@talkprem Updated verison, i have modified it to sort based on ASC/DESC you can control that as you by changing the parameter inside CONCATENATEX.

Unique ID String = 
IF ( 
    ISINSCOPE ( report_table[site_id] ),
    VAR NumberCount =
        ADDCOLUMNS (
            SUMMARIZE ( report_table, report_table[site_id], report_table[value] ),
            "@Number Count",
                LEN ( report_table[value] ) - LEN ( SUBSTITUTE ( report_table[value], ",", "" ) ) + 1
        )
    VAR MaxNumberCount =
        MAXX ( NumberCount, [@Number Count] )
    VAR GenerateNumberSeries = 
        SELECTCOLUMNS ( 
            GENERATESERIES ( 1, MaxNumberCount, 1 ), 
            "@Int", [value] 
        )
    VAR TempTable =
        FILTER (
            GENERATE ( NumberCount, GenerateNumberSeries ),
            [@Int] <= [@Number Count]
        )
    VAR SplitTextByNumber =
        ADDCOLUMNS (
            TempTable,
            "@Final String", INT ( TRIM ( PATHITEM ( SUBSTITUTE ( report_table[value], ",", "|" ), [@Int], TEXT ) ) )
        )
    VAR DistinctNumbers = 
        DISTINCT ( 
            SELECTCOLUMNS ( 
                SplitTextByNumber,
                [@Final String]
            )
        )
    VAR Result = 
        CONCATENATEX ( 
            DistinctNumbers,
            [@Final String],
            ", ",
            [@Final String], DESC
        )
    RETURN 
        Result
)

[talkprem · Accepted solution]

After some debugging the DAX code, I found that

MaxNumberCount
variable is not working and the code fails to work.

after looking at my data i found that max number of comma separate values in a single row are 5. So I hard coded the number 10 and code is working as per expectations. Till 10 iterations. Many thanks and Shoutout to @AntrikshSharma for helping out.

Final code which is working fine for me (this can work even with null values in value column) -

Unique ID String null ok = 
IF ( 
    ISINSCOPE ( report_table[site_id] ),
    VAR NumberCount =
        ADDCOLUMNS (
            SUMMARIZE ( report_table, report_table[site_id], report_table[value] ),
            "@Number Count",
                LEN ( report_table[value] ) - LEN ( SUBSTITUTE ( report_table[value], ",", "" ) ) + 1
        )
  --  VAR MaxNumberCount =
  --      MAXX ( NumberCount, [@Number Count] )
    VAR GenerateNumberSeries = 
        SELECTCOLUMNS ( 
            GENERATESERIES ( 1, 10, 1 ), 
            "@Int", [value] 
        )
    VAR TempTable =
        FILTER (
            GENERATE ( NumberCount, GenerateNumberSeries ),
            [@Int] <= [@Number Count]
        )
    VAR SplitTextByNumber =
        ADDCOLUMNS (
            TempTable,
            "@Final String", INT ( TRIM ( PATHITEM ( SUBSTITUTE ( report_table[value], ",", "|" ), [@Int], TEXT ) ) )
        )
    VAR DistinctNumbers = 
        DISTINCT ( 
            SELECTCOLUMNS ( 
                SplitTextByNumber,
                [@Final String]
            )
        )
    VAR Result = 
        CONCATENATEX ( 
            DistinctNumbers,
            [@Final String],
            ", ",
            [@Final String], DESC
        )
    RETURN 
        Result
)

[AntrikshSharma]

@talkprem 

Unique ID String = 
IF ( 
    ISINSCOPE ( t[id] ),
    VAR NumberCount =
        ADDCOLUMNS (
            t,
            "@Number Count",
                LEN ( t[value] ) - LEN ( SUBSTITUTE ( t[value], ",", "" ) ) + 1
        )
    VAR MaxNumberCount =
        MAXX ( NumberCount, [@Number Count] )
    VAR GenerateNumberSeries = 
        SELECTCOLUMNS ( 
            GENERATESERIES ( 1, MaxNumberCount, 1 ), 
            "@Int", [value] 
        )
    VAR TempTable =
        FILTER (
            GENERATE ( NumberCount, GenerateNumberSeries ),
            [@Int] <= [@Number Count]
        )
    VAR SplitTextByNumber =
        ADDCOLUMNS (
            TempTable,
            "@Final String", TRIM ( PATHITEM ( SUBSTITUTE ( t[value], ",", "|" ), [@Int], TEXT ) )
        )
    VAR DistinctNumbers = 
        DISTINCT ( 
            SELECTCOLUMNS ( 
                SplitTextByNumber,
                [@Final String]
            )
        )
    VAR Result = 
        CONCATENATEX ( 
            DistinctNumbers,
            [@Final String],
            ", "
        )
    RETURN 
        Result
)

[talkprem]

Hi AntrikshSharma,

I have tried the dax query you posted but i got an error,
"the arguments of a generateseries function cant be blank"
i have 5 column like this

plate	datetime	material	site_id	value
123	6/17/2024	345	10023	123,456,789
123	6/17/2024	345	10023	123,456
123	6/17/2024	345	10023	456,789

 to this 

plate	datetime	material	site_id	value
123	6/17/2024	345	10023	123,456,789

can you have a look in the dax query once please table name "report_table"

[AntrikshSharma · Accepted solution]

@talkprem Updated verison, i have modified it to sort based on ASC/DESC you can control that as you by changing the parameter inside CONCATENATEX.

Unique ID String = 
IF ( 
    ISINSCOPE ( report_table[site_id] ),
    VAR NumberCount =
        ADDCOLUMNS (
            SUMMARIZE ( report_table, report_table[site_id], report_table[value] ),
            "@Number Count",
                LEN ( report_table[value] ) - LEN ( SUBSTITUTE ( report_table[value], ",", "" ) ) + 1
        )
    VAR MaxNumberCount =
        MAXX ( NumberCount, [@Number Count] )
    VAR GenerateNumberSeries = 
        SELECTCOLUMNS ( 
            GENERATESERIES ( 1, MaxNumberCount, 1 ), 
            "@Int", [value] 
        )
    VAR TempTable =
        FILTER (
            GENERATE ( NumberCount, GenerateNumberSeries ),
            [@Int] <= [@Number Count]
        )
    VAR SplitTextByNumber =
        ADDCOLUMNS (
            TempTable,
            "@Final String", INT ( TRIM ( PATHITEM ( SUBSTITUTE ( report_table[value], ",", "|" ), [@Int], TEXT ) ) )
        )
    VAR DistinctNumbers = 
        DISTINCT ( 
            SELECTCOLUMNS ( 
                SplitTextByNumber,
                [@Final String]
            )
        )
    VAR Result = 
        CONCATENATEX ( 
            DistinctNumbers,
            [@Final String],
            ", ",
            [@Final String], DESC
        )
    RETURN 
        Result
)

[talkprem]

@AntrikshSharma We have

values like " " and NULLS which are causing leading or trailing "," (commas to be added) in the output. 
could you please help me in this brother?

current output 

id	value
10023	,123,456,789,
10023	123,456,789,

expected output

id	value
10023	123,456,789
10024	123,456,789

thanks in advance.

[talkprem]

Hi,

Thanks for replying but again i am getting the same error
"the arguments of a generateseries function cant be blank"
Unable to understand why this error is coming.
Respect to you brother for helping out.

[AntrikshSharma]

@talkprem Download the file from here and then you can try to replicate in your model: https://drive.google.com/file/d/1pf84uc6YM_zTGuI5IDOOjhFnrY49AMe_/view?usp=sharing

[talkprem]

In my data there are few rows which have NULL data in the value column. Due to this i am getting an error. Could you please add a row in your data having null in value column and check once?
once i added the null value record for the same id it got the same error.

[talkprem]

Hi Antrishk,

Thank you for helping out i tried to use the measure but got an error , "the arguments of generate series function cant be blank". i actually have few more columns which i need to show in this manner.

id number	date	id	material	value
D7765	6/17/2024	10023	4002	123,456
D7765	6/17/2024	10023	4002	123,456,789
D7765	6/17/2024	10023	4002	456,789

to something like this

id number	date	id	material	value
D7765	6/17/2024	10023	4002	123,456,789

can you please help me, many many thanks and respect in advance.