topic Re: Power BI DAX help in DAX Commands and Tips

Power BI DAX help

zenbex — Thu, 05 Jan 2023 21:06:52 GMT

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

Tag_Jira

ACS-103352

AMSENT

JIRA-

JIRA_ATLAS

JIRA_DCOPS

JIRA-00000

JIRA-ACREQ

JIRA-ACS

JIRA-ACSS

JIRA-ACTEAM

JIRA-ADC

JIRA-ADCS

JIRA-AE

JIRA-AEMSTREQ

JIRA-AEPCMM

JIRA-AEPCSSRE

JIRA-AEPE

JIRA-AERO

JIRA-AGENTS

JIRA-AI

JIRA-AIQ

JIRA-ALCT

JIRA-ALFD

JIRA-AMO

JIRA-AMOREQ

JIRA-AMSAUTO

JIRA-AMSBSC

JIRA-AMSCC

JIRA-AMSE

JIRA-AMSEH

Re: Power BI DAX help

adudani — Thu, 05 Jan 2023 21:10:58 GMT

Hi zenbex,

You can create a calculated column in power query.

paste the code below in a blank query for my solution:

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.Contains([Tag_Jira],"JIRA") then "Yes" else "No")
in
#"Added Custom"

Please let me know if this resolves the question.

Appreciate a thumbs up if this is helpful.

Re: Power BI DAX help

zenbex — Thu, 05 Jan 2023 21:18:33 GMT

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

Re: Power BI DAX help

adudani — Thu, 05 Jan 2023 21:36:32 GMT

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"

Re: Power BI DAX help

zenbex — Thu, 05 Jan 2023 21:48:17 GMT

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

Re: Power BI DAX help

adudani — Thu, 05 Jan 2023 22:02:46 GMT

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")