Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
abhiram342
Microsoft Employee
Microsoft Employee

DAX distinct count measure on Pipe separated string

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

DateProductIDSalesAmount
11/1/2022789100
11/1/2022567200
12/1/2022789600
1/1/2023567300
2/1/2023345400

 

FactProductTagHistory

DateProductIDTag
11/1/2022789ABC | DEF
11/1/2022567ABC
12/1/2022789 GLK
1/1/2023567ABC | DEF
2/1/2023345GLK

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

DateProductIDTagSalesAmount
11/1/2022789ABC | DEF100
11/1/2022567ABC200
12/1/2022789GLK600
1/1/2023567ABC | DEF300
2/1/2023345GLK400

 

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

11/1/20222 (ABC, DEF)
12/1/20221 (GLK)
1/1/20232 (ABC, DEF)
2/1/20231 (GLK)

 

Thanks,

Abhiram

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1674717192015.png

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

 

View solution in original post

6 REPLIES 6
wdx223_Daniel
Super User
Super User

wdx223_Daniel_0-1674717192015.png

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

 

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

abhiram342_0-1675314186842.png

 

......

RETURN

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

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.

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

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

ErrorError

 

Error:

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.