DAX formula to detect only specific text

[Lucia]

Hi, I need to add helper column in my query that only addresses the cells that contain the word combination "Controledatum verstreken" and ignores all others. See file attached, it shows the desired outcome. Because this word combination exists in different positions within the cells and is sometimes connected to another word without a space, I don't know how to filter them out. Hopefully someone here does! Tnx.

 

[AlanSidman]

Used Mcode in Power Query which may or may not work for you. I am not fully fluent in DAX.

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Original column", type text}, {"Desired result", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Desired result"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Desired ", each if Text.Contains([Original column],"Controledatum verstreken") then 1 else null),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Desired ", null}})
in
    #"Replaced Errors"

 

[Lucia]

Tnx Alan, for your quick response. I am gonna try it first within excel power query. I am a newbie. Within the query, I chose Add column and then pasted your code. I shows no errors and adds a new table with the correct answer. But when I hit close and load is starts loading and loading. Is it possible within this forum to send me your solution in de form of an excel attachment that I can analyse to see how you did it? Or else, can you guide me through the steps? Thanks again!

 

[AlanSidman]

I had the same issue. It is because we both included the whole column A when bringing it into PQ which is formatted for the entire column. Try limiting the table to just the rows you want to include in your analysis. Highlight the area to be included and then select Ctl + T to make it a table. You may have to remove the first table to make this happen. Then run the code making sure to change the table name if necessary.

 

[GraH - Guido]

Hi Lucia,

Don't know what you need exactly, but do have a look at this DAX.

 

[Chihiro]

@Lucia
A question, you need this as calculated column? Or as DAX measure?

If in a measure, you can use SEARCH function. But do note that you need to ensure you add 4th argument for this to work. Otherwise, you will get null returned (i.e. error).

CountPartialMatch:=
CALCULATE (
    COUNTROWS ( Tabel1 ),
    FILTER (
        Tabel1,
        SEARCH ( "controledatum verstreken", [Original column],, 0 ) > 0
    )
)

 

[GraH - Guido]

Reading the thread again and seeing @Chihiro 's solution... Should it not be an and construction with 2 searches? Words can be swapped it seems.

 

[Lucia]

Hi @GraH - Guido and @Chihiro, thank you for responding! I will add a simpler, fictive example to clarify what I am trying to resolve. Also thank you @AlanSidman. I do think that some of your initial code will be part of the road to get to the answer.

 

[Chihiro]

So, from your export. You'd use PQ to combine data into single table.

However, you need to differentiate each month's data. To do this, I'd recommend naming your table with Month name included.
Ex: Tabel012021, Tabel022021 etc.

That way you can query the workbook. Either using Excel.Workbook(File.Contents("Path"),null,ture) when you are querying external workbook or Excel.CurrentWorkbook(), when you are querying data within workbook.

Then use text manipulation to convert it into date value.
Ex:

=Date.From(Text.End([Name],4) & "-" & Text.Middle([Name],5,2) & "-01")

Change data type as required. Load to data model.

Then add the DAX measure in model.

HBP :=
CALCULATE (
    COUNTROWS ( Query1 ),
    FILTER ( Query1, SEARCH ( "high blood pressure", [Status],, 0 ) > 0 )
)

Result:


See attached.

 

[Lucia]

Perfect! Thank you @Chihiro! See attached image. This was the pivot table I was looking for.