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

     

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

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: