Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Count repeated values in a row

Hi All,    I have column in data where values are repeating.  Can you please suggest me DAX measure or column  by which I can Calculate the the number of times a value is repeating in a row.    C...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    Here is a sample file with the solution https://www.dropbox.com/t/tbRUhGvyoNDAZ5xL
    I believe a fount a solution for your problem. It is a long code but it works just fine for upto 15 products per row. It can be expanded further if needed.
    Please check and let me know if it works with you.
    Here is how the table and report look like

    And here is the code of the calculated column

    Products = 
VAR Table1 = 
    SELECTCOLUMNS ( { ( [Customer ID], [Product Path] ) }, "Customer ID", [Customer ID], "Product Path", [Product Path] )
VAR Table2 =
    GENERATE ( 
        Table1,
        VAR Length = LEN ( Store[Product Path] )
        VAR Loc1 = IFERROR ( FIND ( ",", Store[Product Path], 1 ), 0 )
        VAR Loc2 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc1 ), 0 )
        VAR Loc3 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc2 ), 0 )
        VAR Loc4 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc3 ), 0 )
        VAR Loc5 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc4 ), 0 )
        VAR Loc6 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc5 ), 0 )
        VAR Loc7 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc6 ), 0 )
        VAR Loc8 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc7 ), 0 )
        VAR Loc9 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc8 ), 0 )
        VAR Loc10 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc9 ), 0 )
        VAR Loc11 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc10 ), 0 )
        VAR Loc12 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc11 ), 0 )
        VAR Loc13 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc12 ), 0 )
        VAR Loc14 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc13 ), 0 )
        VAR Loc15 = IFERROR ( FIND ( ",", Store[Product Path], 1 + Loc14 ), 0 )
        VAR P1 = IF ( Loc1 = 0, Store[Product Path], MID ( Store[Product Path], 1 , Loc1 - 1 ) )
        VAR P2 = IF ( Loc2 = 0, IF ( Loc1 <> 0, MID ( Store[Product Path], 1 + Loc1, Length - Loc1 ) ), MID ( Store[Product Path], 1 + Loc1, Loc2 - Loc1 - 1 ) )
        VAR P3 = IF ( Loc3 = 0, IF ( Loc2 <> 0, MID ( Store[Product Path], 1 + Loc2, Length - Loc2 ) ), MID ( Store[Product Path], 1 + Loc2, Loc3 - Loc2 - 1 ) )
        VAR P4 = IF ( Loc4 = 0, IF ( Loc3 <> 0, MID ( Store[Product Path], 1 + Loc3, Length - Loc3 ) ), MID ( Store[Product Path], 1 + Loc3, Loc4 - Loc3 - 1 ) )
        VAR P5 = IF ( Loc5 = 0, IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc4, Length - Loc4 ) ), MID ( Store[Product Path], 1 + Loc4, Loc5 - Loc4 - 1 ) )
        VAR P6 = IF ( Loc6 = 0, IF ( Loc5 <> 0, MID ( Store[Product Path], 1 + Loc5, Length - Loc5 ) ), MID ( Store[Product Path], 1 + Loc5, Loc6 - Loc5 - 1 ) )
        VAR P7 = IF ( Loc7 = 0, IF ( Loc6 <> 0, MID ( Store[Product Path], 1 + Loc6, Length - Loc6 ) ), MID ( Store[Product Path], 1 + Loc6, Loc7 - Loc6 - 1 ) )
        VAR P8 = IF ( Loc8 = 0, IF ( Loc7 <> 0, MID ( Store[Product Path], 1 + Loc7, Length - Loc7 ) ), MID ( Store[Product Path], 1 + Loc7, Loc8 - Loc7 - 1 ) )
        VAR P9 = IF ( Loc9 = 0, IF ( Loc8 <> 0, MID ( Store[Product Path], 1 + Loc8, Length - Loc8 ) ), MID ( Store[Product Path], 1 + Loc8, Loc9 - Loc8 - 1 ) )
        VAR P10 = IF ( Loc10 = 0, IF ( Loc9 <> 0, MID ( Store[Product Path], 1 + Loc9, Length - Loc9 ) ), MID ( Store[Product Path], 1 + Loc9, Loc10 - Loc9 - 1 ) )
        VAR P11 = IF ( Loc11 = 0, IF ( Loc10 <> 0, MID ( Store[Product Path], 1 + Loc10, Length - Loc10 ) ), MID ( Store[Product Path], 1 + Loc10, Loc11 - Loc10 - 1 ) )
        VAR P12 = IF ( Loc12 = 0, IF ( Loc11 <> 0, MID ( Store[Product Path], 1 + Loc11, Length - Loc11 ) ), MID ( Store[Product Path], 1 + Loc11, Loc12 - Loc11 - 1 ) )
        VAR P13 = IF ( Loc13 = 0, IF ( Loc12 <> 0, MID ( Store[Product Path], 1 + Loc12, Length - Loc12 ) ), MID ( Store[Product Path], 1 + Loc12, Loc13 - Loc12 - 1 ) )
        VAR P14 = IF ( Loc14 = 0, IF ( Loc13 <> 0, MID ( Store[Product Path], 1 + Loc13, Length - Loc13 ) ), MID ( Store[Product Path], 1 + Loc13, Loc14 - Loc13 - 1 ) )
        VAR P15 = IF ( Loc15 = 0, IF ( Loc14 <> 0, MID ( Store[Product Path], 1 + Loc14, Length - Loc14 ) ), MID ( Store[Product Path], 1 + Loc14, Loc15 - Loc14 - 1 ) )
        --VAR P3 = IF ( Loc3 <> 0, MID ( Store[Product Path], 1 + Loc2, Loc3 - Loc2 - 1 ), IF ( Loc2 <> 0, MID ( Store[Product Path], 1 + Loc2, Length - Loc2 ) ) )
        --VAR P4 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc3, Loc4 - Loc3 - 1), IF ( Loc3 <> 0, MID ( Store[Product Path], 1 + Loc3, Length - Loc3 ) ) )
        --VAR P5 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc4, Loc5 - Loc4 - 1), IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc4, Length - Loc4 ) ) )
        --VAR P6 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc5, Loc6 - Loc5 - 1), IF ( Loc5 <> 0, MID ( Store[Product Path], 1 + Loc5, Length - Loc5 ) ) )
       -- VAR P7 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc6, Loc7 - Loc6 - 1), IF ( Loc6 <> 0, MID ( Store[Product Path], 1 + Loc6, Length - Loc6 ) ) )
        --VAR P8 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc7, Loc8 - Loc7 - 1), IF ( Loc7 <> 0, MID ( Store[Product Path], 1 + Loc7, Length - Loc7 ) ) )
        --VAR P9 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc8, Loc9 - Loc8 - 1), IF ( Loc8 <> 0, MID ( Store[Product Path], 1 + Loc8, Length - Loc8 ) ) )
        --VAR P10 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc9, Loc10 - Loc9 - 1), IF ( Loc9 <> 0, MID ( Store[Product Path], 1 + Loc9, Length - Loc9 ) ) )
        --VAR P11 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc10, Loc11 - Loc10 - 1), IF ( Loc10 <> 0, MID ( Store[Product Path], 1 + Loc10, Length - Loc10 ) ) )
        --VAR P12 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc11, Loc12 - Loc11 - 1), IF ( Loc11 <> 0, MID ( Store[Product Path], 1 + Loc11, Length - Loc11 ) ) )
        --VAR P13 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc12, Loc13 - Loc12 - 1), IF ( Loc12 <> 0, MID ( Store[Product Path], 1 + Loc12, Length - Loc12 ) ) )
        --VAR P14 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc13, Loc14 - Loc13 - 1), IF ( Loc13 <> 0, MID ( Store[Product Path], 1 + Loc13, Length - Loc13 ) ) )
        --VAR P15 = IF ( Loc4 <> 0, MID ( Store[Product Path], 1 + Loc14, Loc15 - Loc14 - 1), IF ( Loc14 <> 0, MID ( Store[Product Path], 1 + Loc13, Length - Loc14 ) ) )
        RETURN
            {  (Loc1, P1), (Loc2, P2), (Loc3, P3), (Loc4, P4), (Loc5, P5), (Loc6, P6), (Loc7, P7), (Loc8, P8), (Loc9, P9), (Loc10, P10), (Loc11, P11), (Loc12, P12), (Loc13, P13), (Loc14, P14), (Loc15, P15) }
    )
VAR Table3 =
    DISTINCT (
        SELECTCOLUMNS ( 
            Table2,
            "Value1", [Value1],
            "Value2", [Value2]
        )
    )
VAR Table4 =
    ADDCOLUMNS (
        Table3, 
        "@Repeated",
        VAR CurrentProduct = [Value2] 
        RETURN
        COUNTROWS (   
            FILTER (
                Table3,
                    [Value2] = CurrentProduct
                        && NOT ISBLANK ( [Value2] )
            )
        )
    )
VAR Table5 =
    SELECTCOLUMNS (
        ADDCOLUMNS ( 
            Table4,
            "@Result", [@Repeated] & [Value2]
        ),
        "Result", [@Result]
    ) 
VAR Result =
    CONCATENATEX ( DISTINCT ( Table5 ), [Result], "," & UNICHAR ( 10 ) )
RETURN 
    Result
AntrikshSharma's avatar
AntrikshSharma
Icon for Community Champion rankCommunity Champion
4 years ago

Anonymous 

 

ChannaCol = 
VAR CurrentPath = Channa[Product Path]
VAR CurrentPathCount =
    LEN ( CurrentPath )
VAR RemoveComma =
    SUBSTITUTE ( CurrentPath, ",", "" )
VAR DelimeterCount =
    1 + CurrentPathCount
        - LEN ( RemoveComma )
VAR CurrentPathLengthSeries =
    SELECTCOLUMNS ( GENERATESERIES ( 1, DelimeterCount, 1 ), "Index", [Value] )
VAR AttachCurrentPathToSeries =
    ADDCOLUMNS (
        CurrentPathLengthSeries,
        "@CurrentAlphabet", MID ( RemoveComma, [Index], 1 )
    )
VAR AlphabetGroups =
    GROUPBY (
        AttachCurrentPathToSeries,
        [@CurrentAlphabet],
        "@Count", COUNTX ( CURRENTGROUP (), [@CurrentAlphabet] )
    )
VAR Result =
    CONCATENATEX ( AlphabetGroups, [@Count] & "-" & [@CurrentAlphabet], ", " )
RETURN
    Result

 

For a measure just change first line to 

VAR CurrentPath = SELECTEDVALUE ( Channa[Product Path] )