DAX distinct count measure on Pipe separated string

[abhiram342]

Hi Team,

I have Fact table (FactSalesTag) with one of column is stored as "|" seperated string. I want to create measure by splitting the column with "|" into seperate string on fly . Can someone please help with dax code for "UniqueTagMeasureCount" in "FactSalesTag"  Table.

Example:

In below Table (FactSalesTag) , Tag is metadata info associated to ProductID over Time. If I split the Column into multiple rows then the SalesAmount will be duplicate hence I'm storing in single Column with "|" delimeter.

FactSales

Date	ProductID	SalesAmount
11/1/2022	789	100
11/1/2022	567	200
12/1/2022	789	600
1/1/2023	567	300
2/1/2023	345	400

FactProductTagHistory

Date	ProductID	Tag
11/1/2022	789	ABC | DEF
11/1/2022	567	ABC
12/1/2022	789	GLK
1/1/2023	567	ABC | DEF
2/1/2023	345	GLK

FactSalesTag ( Joined FactSales and FactProductTagHistory based on Date and ProductID)

Date	ProductID	Tag	SalesAmount
11/1/2022	789	ABC | DEF	100
11/1/2022	567	ABC	200
12/1/2022	789	GLK	600
1/1/2023	567	ABC | DEF	300
2/1/2023	345	GLK	400

UniqueTagMeasureCount ==> Please help wiht DAX code for this measure

11/1/2022	2 (ABC, DEF)
12/1/2022	1 (GLK)
1/1/2023	2 (ABC, DEF)
2/1/2023	1 (GLK)

Thanks,

Abhiram

[wdx223_Daniel · Accepted solution]

=
VAR _p =
CONCATENATEX ( VALUES ( FactSalesTag[Tag] ), 'FactSalesTag'[Tag], "|" )
VAR _t =
ADDCOLUMNS (
GENERATESERIES ( 1, PATHLENGTH ( _p ) ),
"@tag", TRIM ( PATHITEM ( _p, [Value] ) )
)
RETURN
COUNTROWS ( SUMMARIZE ( _t, [@tag] ) )

[abhiram342]

Hi @wdx223_Daniel - I'm using Visual Studio Editor and FactSalesTag is empty. I have processed model after deploying to server. Can you please help

[wdx223_Daniel]

......

RETURN

IF(_SplitData="",0,COUNTROWS(....))

[abhiram342]

Thanks @wdx223_Daniel for your response. I'm still getting same error. I can see the data Temp but when I drag related columns . example Date Dimension then it gives error as below. Is there any better way to debug this issue. Please let me know

BackEnd Query:

EVALUATE
SUMMARIZECOLUMNS(
    'Date'[Fiscal Month],
    'Date'[Month Key],
    "UniqueTagMeasureCount", [UniqueTagMeasureCount]
)
ORDER BY 
    'Date'[Fiscal Month] ASC,
    'Date'[Month Key] ASC

Error:

The arguments in GenerateSeries function cannot be blank.

[wdx223_Daniel]

IF(_SplitData=""||_SplitData="|",0,COUNTROWS(....))

[abhiram342]

Hi @wdx223_Daniel  - Thank you for your response. I'm getting below error when I drag the column from dimension Table ( ex: Month from Date Table , UniqueMeasureCount) . UniqueMeasureCount works fine but throwing error when I drag columns from related tables. Can you please help

Error

Error: