Power bi: Count one value in an array of values

[iglesias6]

I have one column in table 1 with an array of values:

How can I count how many rows have "1.0.0.0", "1.1.0.0" or "None" values? And how can i count how many times they are repeated?

Important Note: I cannot split the column "RelatedFixVersion"

[Ahmedx]

Hi! i have optimized my code try it

m3 = 
VAR _TextSearch = "1.0.0.0" // This is the value we are searching for
VAR _Delimiter = ","
VAR _tbl = ADDCOLUMNS ( 'Table', "@patch", SUBSTITUTE ( [Related Fix Version], _Delimiter, "|" ))  
-- Creating a new table '_tbl' with an added '@patch' column by replacing ',' with '|' in the 'Related Fix Version' column

VAR _len =  MAXX(_tbl, PATHLENGTH([@patch]))
-- Calculating the maximum path length (maximum number of items in the path) in the '@patch' column of '_tbl'

VAR _Filter =  FILTER( 
    ADDCOLUMNS(
        GENERATE (_tbl, GENERATESERIES ( 1, _len)),
        "@ext", PATHITEM([@patch], [Value], TEXT)
    ),
    [@ext] = _TextSearch
)
-- Creating a filtered table '_Filter' by generating a series of numbers and extracting path elements from the '@patch' column.
-- Then, filtering for rows where '@ext' equals '_TextSearch'

RETURN
   COUNTROWS(_Filter)  
-- Returning the count of rows in the '_Filter' table, representing the number of rows that satisfy the filter condition.

[parry2k]

@iglesias6 @Ahmedx has provided an amazing solution, here is another version, add as a DAX table:

Table 3 = 
VAR __Table = 
GENERATEALL ( 
    'Table',
    VAR __v = SUBSTITUTE ( 'Table'[Related Fix Version], ",", "|" )
    RETURN
    ADDCOLUMNS ( 
        GENERATESERIES ( 1, PATHLENGTH ( __v ) ), 
        "Segment",  VAR __segment = [Value] RETURN PATHITEM ( __v, __segment ) 
    )
)

VAR __result = GROUPBY ( __Table, [Segment], "Count", COUNTX ( CURRENTGROUP (), [Segment] ) )
RETURN __result

This will return summarized data, but if you want detailed list then return __Table

[Ahmedx · Accepted solution]

pls try this

m1 = 
COUNTROWS(FILTER(ALL('Table'),CONTAINSSTRING([Related Fix Version],"1.0.0.0")))

m2 = 
COUNTROWS(FILTER(ALL('Table'),CONTAINSSTRING([Related Fix Version],"None")))


m3 = 
COUNTROWS(FILTER(ALL('Table'),CONTAINSSTRING([Related Fix Version],"1.1.0.0")))

[iglesias6]

Yes! It works!!! That helps me a lot! Thanks

And if i want to count the number of times "1.0.0.0" is repeated (not counting one per row). How would you do it?

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

https://1drv.ms/u/s!AiUZ0Ws7G26RiituxNgOe-CnK8nb?e=H0ZnG3

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.