Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

How to create a measure to output the sum, only if it contains certain characters

Hi there, I am stuck on creating a measure based on a column where I have to output the sum of that column whether it includes or not includes certain charachters. 

 

For example say that some of the rows in this column say 12345-NR, but some other rows in that same column just say 12345. 

 

How can I get the sum of the rows in that column that include rows only when it ends in "NR" on a seperate measure, and another seperate measure that sums the same column only when the rows do not contain "NR" at the end.

 

This is the measure (incorrect) i have created when it includes NR:

Non-recurring = CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, Backlog[Project ID] <> "NR"))
 
Atleast maybe if that measure does work, how would I use that same measure formula in a new measure and say only when it does not include NR.
 
Thanks!
1 ACCEPTED SOLUTION
smpa01
Super User
Super User

@Anonymous 

 

 

no NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, RIGHT(Backlog[Project ID],2) <> "NR"))
only NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, RIGHT(Backlog[Project ID],2) = "NR"))

/if NR is contained anywhere in the string
no NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, CONTAINSSTRING('Table'[Column1],"NR") =FALSE()))
only NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, CONTAINSSTRING('Table'[Column1],"NR") =TRUE()))

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

3 REPLIES 3
smpa01
Super User
Super User

@Anonymous 

 

 

no NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, RIGHT(Backlog[Project ID],2) <> "NR"))
only NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, RIGHT(Backlog[Project ID],2) = "NR"))

/if NR is contained anywhere in the string
no NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, CONTAINSSTRING('Table'[Column1],"NR") =FALSE()))
only NR= CALCULATE(SUM(Backlog[Remain Cdn Ttl Price]), FILTER(Backlog, CONTAINSSTRING('Table'[Column1],"NR") =TRUE()))

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

@smpa01 how are you saying "it does not contain"? I thought using "<>" meant contains

Anonymous
Not applicable

Thanks so much !

 

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.