Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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. 🙂
Solved! Go to 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
)
)
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
)
)
@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"
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@zudar
You can easily do it in Power Query, add a custom column. I have attached the file below my signature.
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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! 🙂
User | Count |
---|---|
102 | |
91 | |
84 | |
77 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |