CODEWITHSUNDEEP
powerbidax
Philip
Philip

Reputation: 2628

Count Specific Word by Row in DAX

I'm trying to count the number of times the word "text" appears per row in Power BI. I've done a lot of google searching and seen examples like this:

Formula :=
CALCULATE (
    COUNTROWS ( FILTER ( 'TestData', FIND ( "text", 'TestData'[Description],, 0 ) > 0 ) ),1=1
)

but it isn't quite getting me there. How can I get for row 1, a result of 1 and row 2, a result of 3.

CREATE TABLE [dbo].[TestData](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [varchar](100) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TestData] ON 
GO
INSERT [dbo].[TestData] ([ID], [Description]) VALUES (1, N'this is my demo text')
GO
INSERT [dbo].[TestData] ([ID], [Description]) VALUES (2, N'text text demo text')
GO
SET IDENTITY_INSERT [dbo].[TestData] OFF
GO

Expected Result

ID     Description               Text Word Count

1      this is my demo text      1
2      text text demo text       3

Upvotes: 1

Views: 840

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60474

I don't believe DAX has a textsplit function, but you can do something like this to ensure you don't pick up words of which text is a substring.

Text Count (DAX) = 
    VAR pad = SUBSTITUTE(" " & [Description] & " ","text","~text~")
    VAR lenPad = LEN(pad)
    VAR lenText = LEN("~text~")
    VAR lenRemText = LEN(SUBSTITUTE(pad,"~text~",""))
RETURN (lenPad-lenRemText)/lenText 
```

Upvotes: 2

Jos Woolley
Jos Woolley

Reputation: 9062

Calculated Column:

=
VAR MySearchText = "text"
RETURN
    DIVIDE(
        LEN( Table1[Description] )
            - LEN( SUBSTITUTE( Table1[Description], MySearchText, "" ) ),
        LEN( MySearchText )
    )

Measure:

=
VAR MySearchText = "text"
VAR ThisDescription =
    MIN( Table1[Description] )
RETURN
    DIVIDE(
        LEN( ThisDescription )
            - LEN( SUBSTITUTE( ThisDescription, MySearchText, "" ) ),
        LEN( MySearchText )
    )

though note that both of these will return positive counts where MySearchText is found within other words: a description of "this is textual", for example, will return 1.

Upvotes: 2

Related Questions