topic Re: DAX distinct count measure on Pipe separated string in DAX Commands and Tips

DAX distinct count measure on Pipe separated string

abhiram342 — Thu, 26 Jan 2023 05:11:21 GMT

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

Re: DAX distinct count measure on Pipe separated string

wdx223_Daniel — Thu, 26 Jan 2023 07:13:25 GMT

=
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] ) )

Re: DAX distinct count measure on Pipe separated string

abhiram342 — Thu, 02 Feb 2023 04:40:39 GMT

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:

Re: DAX distinct count measure on Pipe separated string

abhiram342 — Thu, 02 Feb 2023 05:03:56 GMT

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

Re: DAX distinct count measure on Pipe separated string

wdx223_Daniel — Thu, 02 Feb 2023 06:41:37 GMT

......

RETURN

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

Re: DAX distinct count measure on Pipe separated string

abhiram342 — Thu, 02 Feb 2023 07:49:35 GMT

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.

Re: DAX distinct count measure on Pipe separated string

wdx223_Daniel — Thu, 02 Feb 2023 08:07:36 GMT

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