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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
abc_777
Super User
Super User

how to remove first two letter

Hi,

I have invoice number like following

 

20221414

20221415

20221416

20221414

20221414

CR20221417

CR20221417

CR20221418

20221420

20221420

 

now i want to distinct count, so discinct count result will be 2 (20221414) and 2 (20221420)

 this wont show 2 (CR20221417)

 

please help me the measure

 

1 ACCEPTED SOLUTION

why you remove CR ? I think you not understand my question.

 

I want a measure that can count distinct  invoice numbers. but not include invoice number starts with CR. 

return result will be like this

 

now i want to distinct count, so discinct count result will be 

distinct count= 2 (20221414) and distinct count= 2 (20221420)

 no need to count  2 (CR20221417)

View solution in original post

5 REPLIES 5
Tahreem24
Super User
Super User

@abc_777 You can create a new column using below DAX:

New Column =
VAR _Length = LEN(TableName[ID])
RETURN IF(_Length=8,'TableName'[ID],RIGHT(TableName[ID],8))
 
Capture.JPG
 
Don't forget to give thumbs up and accept this as a solution if it helped you!!!

Please take a quick glance at newly created dashboards : Restaurant Management Dashboard , HR Analytics Report , Hotel Management Report, Sales Analysis Report , Fortune 500 Companies Analysis , Revenue Tracking Dashboard

why you remove CR ? I think you not understand my question.

 

I want a measure that can count distinct  invoice numbers. but not include invoice number starts with CR. 

return result will be like this

 

now i want to distinct count, so discinct count result will be 

distinct count= 2 (20221414) and distinct count= 2 (20221420)

 no need to count  2 (CR20221417)

TheoC
Super User
Super User

Hi @abc_777 

 

1. Go into Power Query via Transform Data on main ribbon.

2. Click on the respective Invoice Number column in Power Query. 

3. Go to Replace Values.

TheoC_0-1643698438056.png

4. Input CR into Value To Find like below. Press ok.

TheoC_1-1643698479077.png

Done! Hope this helps 🙂

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

if i remove CR then how i use this CR for other measure. I also need this CR for other measure not want to remove parmanently. I want to create measure to remove cr for a specific measure and then distinct count

@abc_777 you can duplicate the column in Power Query and then remove the CR from the duplicate. 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?www.linkedin.com/in/theoconias

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.