Forum Discussion

zenbex's avatar
zenbex
New Member
3 years ago
Solved

Power BI DAX help

Dear Friends,   I have data set like below. i want check if the column have value Like "JIRA-XXXX. X-Can be text or number. if the text is JIRA-xxxx True else False.  Thank you for your help   ...
  • adudani's avatar
    adudani
    3 years ago

    Thanks for clarifying.

     

    try this.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc89DsIwDAXgu2SmUqHiAMYJ0KqmITZTVVWdECv3H/iTqJ+n90mOnjOOwZb73D2eS5g2YyDWals3zX73o2i62Dd2baHqn2aynnRl5CGvrOrPrCQu6eqpAJAlEufIAL+afBY17EiZRdCqBd5kUBmcTu9v+67WZ1/Ts3kdo5MMALxPlG4GC3pQBjMyoc5hml4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Tag_Jira", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.StartsWith([Tag_Jira],"JIRA-") and Text.Length([Tag_Jira])=10 then "yes" else "no")
    in
    #"Added Custom"

  • adudani's avatar
    adudani
    3 years ago

    if you want a calculated column in the data view, 

     

    try =
    if ( LEFT('Table (3)'[Tag_Jira],5)= "JIRA-" && LEN('Table (3)'[Tag_Jira])=10, "yes","no")

     

zenbex's avatar
zenbex
New Member
3 years ago

Thank you for fast reply. but my quetion was a value the begins with "JIRA-" and five character only will be yes other wise it will be NO

 

 

 

adudani's avatar
adudani
Icon for Memorable Member rankMemorable Member
3 years ago

Thanks for clarifying.

 

try this.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc89DsIwDAXgu2SmUqHiAMYJ0KqmITZTVVWdECv3H/iTqJ+n90mOnjOOwZb73D2eS5g2YyDWals3zX73o2i62Dd2baHqn2aynnRl5CGvrOrPrCQu6eqpAJAlEufIAL+afBY17EiZRdCqBd5kUBmcTu9v+67WZ1/Ts3kdo5MMALxPlG4GC3pQBjMyoc5hml4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Tag_Jira", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if Text.StartsWith([Tag_Jira],"JIRA-") and Text.Length([Tag_Jira])=10 then "yes" else "no")
in
#"Added Custom"

  • zenbex's avatar
    zenbex
    New Member
    3 years ago

    Thank you so much this one is correct. is there a way i can use from the existing table columns ? like i have table[DimJira] where Tag_Jira column Available

     

    thanks