Forum Discussion

abc_777's avatar
abc_777
Icon for Solution Specialist rankSolution Specialist
4 years ago
Solved

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

 

Need Help
  • abc_777's avatar
    abc_777
    4 years ago

    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)

5 Replies

  • TheoC's avatar
    TheoC
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.

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

    Done! Hope this helps 🙂

    • abc_777's avatar
      abc_777
      Icon for Solution Specialist rankSolution Specialist
      4 years ago

      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

      • TheoC's avatar
        TheoC
        Icon for Community Champion rankCommunity Champion
        4 years ago

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

  • Tahreem24's avatar
    Tahreem24
    Icon for Super User rankSuper User
    4 years ago

    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))
     
     
    • abc_777's avatar
      abc_777
      Icon for Solution Specialist rankSolution Specialist
      4 years ago

      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)