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 - I'm using Visual Studio Editor and FactSalesTag is empty. I have processed model after deploying to server. Can you please help

 

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago

    ......

    RETURN

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

    • 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(....))