Filtering by first letter of job number

[Pmorg73]

Hi I am a newbie, so bear with me

I have a table of jobs. The fee earning jobs are pure numbers, ie 1904-2005.

My admin tasks all start with a J, ie J00001 = Admin (several of these)

My job proposals all start with P, ie P1906-2006

I am assuming I need measures to be able distinguish totals, count type functions to the 3 distinct groups? How do I for example sum all time spent on "P" prefixed jobs? 

It is easy to filter individual tables/visuals for the prefix, but I will need to use the total values for each in other reports later on.

Side note: I tried to prefilter the data tables into 3 different tables with the same columns and to build a relationship between them, but I always fail once I get more than 2 tables. Any good guidence on relationships out there? I find it baffling that my relationships fall down

[Anonymous]

Hi @Pmorg73 

Create a new column with the first character.

Then use it in your filters.

e.g

First_Char = LEFT(Task_Code, 1) 

After that use in your measures.

Calculate(Countrows(Table), Filter(Table, First_Char = "J"))

Thanks!
A

[Pmorg73]

The first bit did not work? Expression.Error: The name 'LEFT' wasn't recognized. Make sure it's spelled correctly

---

@Anonymous wrote:

Hi @Pmorg73 

Create a new column with the first character.

Then use it in your filters.

e.g

First_Char = LEFT(Task_Code, 1) 

After that use in your measures.

Calculate(Countrows(Table), Filter(Table, First_Char = "J"))

Thanks!
A

---

---

@Anonymous wrote:

Hi @Pmorg73 

Create a new column with the first character.

Then use it in your filters.

e.g

First_Char = LEFT(Task_Code, 1) 

After that use in your measures.

Calculate(Countrows(Table), Filter(Table, First_Char = "J"))

Thanks!
A

---

[Anonymous]

Hey @Pmorg73 

It is hard to guess what you have done.

It works perfectly for me.

Thanks!
A

[Pmorg73]

---

@Anonymous wrote:

Hey @Pmorg73 

It is hard to guess what you have done.

It works perfectly for me.

Thanks!
A

---

Am I adding this to the table in query editor? 

[Anonymous · Accepted solution]

@Pmorg73 

No. That is DAX.

Right click the table name and add new column.

Thanks

A

[Pmorg73]

that was where I was falling. I did it in query editor and Left is not a function in there. Thanks buddy