• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

DAX formula to detect only specific text

Lucia

New Member
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.
 

Attachments

AlanSidman

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

Code:
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

New Member
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

Well-Known Member
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.
 

Chihiro

Excel Ninja
@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).

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

Chihiro

Excel Ninja
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:
Code:
=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.

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

See attached.
 

Attachments

Top