Forum Discussion

juhoneyighot's avatar
juhoneyighot
Icon for Helper III rankHelper III
2 years ago
Solved

Get the Characters from a text using DAX

Hello!

I need help to get the characters from a certain text using DAX. I have this Column "JobType". See table below.

JobtypeNC.INPhaseStatus
NC 120_Rough Builder Walk__10Not StartedNCNC 120_Rough Builder Walk10Not Started
NC.IN 720_Dehu_Estimate__90CompleteNC.INNC.IN 720_Dehu_Estimate90Complete
IN 622_CO EQ_QC__90CompleteININ 622_CO EQ_QC90Complete
Install_Estimates   

 I have to use a DAX to get the characters for the Phase and Status Column from Jobtype column with this condition. If Column NC.IN is "" then ""

 

else 

 

Phase column is extracted before the __(2underscores)

Status column is extracted after the __(2underscores)

 

I am confused what correct DAX formula to use. 

 

Thank you.

Tips and Tricks

8 Replies

  • DOLEARY85's avatar
    DOLEARY85
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    Hi,

     

    try these in custom columns:

     

    Phase= If ([NC.IN] = "", "", left([Jobtype], search("__",[Jobtype],,0)))
     
    Status= If ([NC.IN] = "", "", right([Jobtype], len([Jobtype]) - search("__",[Jobtype],,0)))
     
    If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
      • DOLEARY85's avatar
        DOLEARY85
        Icon for Resident Rockstar rankResident Rockstar
        2 years ago

        I assume you're using direct query mode which is going to make it difficult with DAX in calculated columns as what you can do is limited, you could try it as a measure but you have to be a little creative:

         

        Phase = If (CALCULATE(max([NC.IN])) = "", "", left(CALCULATE(max([Jobtype])), search("__",CALCULATE(max([Jobtype])),,0)))
         
         
        If this doesn't work you might be better creating a view in the source with the additional fields and then connecting that view to Power BI.
         
        If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
  • Ahmedx's avatar
    Ahmedx
    Icon for Super User rankSuper User
    2 years ago

    pls try this

    Status = IF(
    NOT ISBLANK( 'Table'[NC.IN] ),
    PATHITEM( SUBSTITUTE( 'Table'[Jobtype], "__", "|" ), 2 ),
    ""
)

      • juhoneyighot's avatar
        juhoneyighot
        Icon for Helper III rankHelper III
        2 years ago

        Ahmedx The solution works for Status Column.

         

        Can I also ask help for the Phase Column. Same condition with Status column but Phase Column is extracted before the 2_(two Underscores).

         

        Thank you.