Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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"
Solved! Go to 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")))
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.
@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
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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")))
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?
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.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |