Forum Discussion

abhiram342's avatar
abhiram342
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago
Solved

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...
"DAX"
Distinct Counts
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago

    =
    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's avatar
abhiram342
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago

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's avatar
wdx223_Daniel
Icon for Community Champion rankCommunity Champion
3 years ago

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