Helper I

2 years ago

Solved

Unique ids from a string using DAX measure

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

2 years ago

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

2 years ago

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
)

9 Replies

AntrikshSharma

Community Champion

2 years ago

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
Helper I
2 years ago
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.

talkprem

Helper I

2 years ago

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

Community Champion

2 years ago

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

Helper I

2 years ago

After some debugging the DAX code, I found that

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

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
)

Forum Discussion

Unique ids from a string using DAX measure

9 Replies

Related content

Concatenate only unique strings in measure

DAX Measure to Concatenate Unique Strings

Measuring similarity between strings

unique value in measure column

Unique Count in Row convert

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