Text based calculated column

[zudar]

Hi all,

I could use some help with this calculated column I'm trying to come up with. Because it's based on text and I'm not used to these types of use-cases, I'm struggling to come up with a good dax-formula.

My data looks like:

Text

the

fox

the quick brown fox

the quick brown fox jumps

the quick brown fox jumps over the lazy dog

sphinx

sphinx of black quartz

black quartz

sphinx of black quartz, judge my vow

jinxed wizards

jinxed wizards pluck ivy

the big quilt

jinxed wizards pluck ivy from the big quilt

I want to create a calculated 'flag' column based on this data which gives a '1' for 'uniqueness' and '0' for text that is already present as 'starting word(s)' in another row. So the difficult part is that the check on presence in another row should be from the first word/letter of that field. So my desired end-result is:

Text	Flag
the	0
fox	1
the quick brown fox	0
the quick brown fox jumps	0
the quick brown fox jumps over the lazy dog	1
sphinx of blue quartz	1
sphinx of black quartz	0
black quartz	1
sphinx of black quartz, judge my vow	1
jinxed wizards	0
jinxed wizards pluck ivy	0
the big quilt	1
jinxed wizards pluck ivy from the big quilt	1

So, for example in the first two rows, 'the' is the starting word(s) of 4 other rows ('the quick brown ...' & 'the big quilt'), but 'fox' is not the starting word(s) of any other row.

Or, for example, 'black quartz' is not present as starting words in another row (though it is present mid-sentence in another row), so it gets flagged. 

I hope I've made myself clear! I have been looking at the dax-function FIND(...), but I'm struggling to implement it according to my use-case.

Really appreciate it if anyone can help me out. 🙂

[askhanduja · Accepted solution]

With your table being named 'Table' and the column being named 'InputText', the following code works to create a calculated column called flag:

flag = 
VAR CurrentRow = 'Table'[InputText]
VAR IsDuplicate = COUNTROWS(FILTER('Table', 'Table'[InputText] = CurrentRow)) > 1
VAR TableWithoutCurrentRow = 
FILTER(
    'Table',
    'Table'[InputText] <> CurrentRow
)
VAR RowsStartingWithCurrentRow =
FILTER(
    TableWithoutCurrentRow,
    SEARCH(CurrentRow, 'Table'[InputText], 1, 0) = 1
)
VAR IsCurrentRowUnique =
ISEMPTY(RowsStartingWithCurrentRow)
RETURN
IF(
    IsDuplicate,
    0,
    IF(
        IsCurrentRowUnique,
        1,
        0
    )
)

[Fowmy]

@zudar 

Add a blank Query in Power Query and past the below code and check the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY/BDoMgEER/ZeLZPyIesCBi0aUoIH69wKFpqvG02Zk3uxnGmm2UTdeyZqC9zrzj4/Xrjd5RXPCgY/KzXZ9dUJAOxTb8SBCkUAOrHfWygwb0xpcsd9txcXg++GNdhHu2za+FkpgTAsUKThmTAlEf3In1RoI1Ph/QIX379FqVTmZ7xDE4mvHHdyc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Flag",  (x)=> 
Number.From(
    List.Count(
        List.Select( #"Changed Type"[Text] , each Text.StartsWith(_,x[Text]))
    ) > 1
    ) 
)
in
    #"Added Custom"

[Fowmy]

@zudar 

You can easily do it in Power Query, add a custom column. I have attached the file below my signature.

[zudar]

Hi @Fowmy !

It appears that I can't open the file properly in my version of PowerBI Desktop. I'm working with the Report Server version of PowerBI Desktop, which is version: 2.88.1382.0 64-bit (januari 2021).

I'm really curious to see what you did. I didn't consider using Power Query, thanks for thinking outside the box! 🙂