Forum Discussion

bilingual's avatar
bilingual
Icon for Helper V rankHelper V
6 years ago
Solved

Related table with different filter functions

Hi i have a data table with following where i have a lot of entries like:

 

ENTRYPART OF ENTRY WHICH DEFINES TYPE
AAHEPXXXXPX
DKDEFXXXXFX
5DKDKE5XXXX
6EOEEM6XXXX
7ENEEG7XXXX
S2OLDCS2OLDC

 

My translation table needs to be able to be able to decode the data with both full, Left and  Right functions. 

In SAP i use If(Substr(Trim([ENTRY]);4;2))="PX") Then "Public export", and then add RIGHT/LEFT functions for every type without a translation table.

 

Could i build a tranlations table in Power BI which would better or is IF(RIGHT/LEFT the only way to solve it?

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi bilingual ,

     

     

    Column = SWITCH(
    TRUE(),
    SEARCH("FX",'Table20'[ENTRY],1,0) > 0 , "Public Export",
     SEARCH("7",'Table20'[ENTRY],1,0) > 0 , "Local Export",
      SEARCH("S2OLDC",'Table20'[ENTRY],1,0) > 0 , "Special Export"
)

     

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

     

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI bilingual 

     

    Not very clear.

    Can you please post sample output or all SAP condition for each row.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi bilingual ,

     

    You can use FIND,SEARCH function and a combine them with LEFT ,RIGHT and MID Function in Power BI.

     

    Can you share some sample data and expected output.

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    bilingual , Not very clear

     

    In DAX

    If(MID(Trim([ENTRY]),4,2)="PX" , "Public export",blank())

     

    in Power Query M
    if Text.Middle(Text.Trim([ENTRY]),4,2)="PX" then "Public export"

  • bilingual's avatar
    bilingual
    Icon for Helper V rankHelper V
    6 years ago

    Hi again, so sorry, can see it is not very clear writtten, i try again with an example, how it is working in SAP:

     

    ENTRYPART OF ENTRY WHICH DEFINES TYPETYPE DEFINED FROM PART OF ENTRYCURRENT SAP FORMULA
    DKDEFX"FX"Public export=If(Substr(Trim([ENTRY]);4;2))="PX") Then "Public export"
    7ENEEG"7"Local export=If(Substr(Trim([ENTRY]);1;1))="7") Then "Local export"
    S2OLDCS2OLDCSpecial export=If(Substr(Trim([ENTRY]);1;6))="S2OLDC") Then "Special export"

     

    All entries have a length of 6 characters

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi bilingual ,

       

      You will need SEARCH ,MID, LEFT functions to extract the value.

       

      Regards,
      Harsh Nathani
      Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi bilingual ,

         

         

        Column = SWITCH(
    TRUE(),
    SEARCH("FX",'Table20'[ENTRY],1,0) > 0 , "Public Export",
     SEARCH("7",'Table20'[ENTRY],1,0) > 0 , "Local Export",
      SEARCH("S2OLDC",'Table20'[ENTRY],1,0) > 0 , "Special Export"
)

         

         

        Regards,
        Harsh Nathani
        Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)