Related table with different filter functions

[bilingual]

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

ENTRY	PART OF ENTRY WHICH DEFINES TYPE
AAHEPX	XXXPX
DKDEFX	XXXFX
5DKDKE	5XXXX
6EOEEM	6XXXX
7ENEEG	7XXXX
S2OLDC	S2OLDC

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?

[bilingual]

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

ENTRY	PART OF ENTRY WHICH DEFINES TYPE	TYPE DEFINED FROM PART OF ENTRY	CURRENT 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"
S2OLDC	S2OLDC	Special export	=If(Substr(Trim([ENTRY]);1;6))="S2OLDC") Then "Special export"

All entries have a length of 6 characters

[harshnathani]

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)

[harshnathani · Accepted solution]

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)

[bilingual]

Hi, very quick question, will it be better to use RIGHT, MID or LEFT in terms of memory compared to SEARCH, when i know the position of each variable?

[pranit828]

HI @bilingual 

Thank you for more detail.

You can try SEARCH()

https://docs.microsoft.com/en-us/dax/search-function-dax

[pranit828]

HI @bilingual 

Thank you for more detail.

Can you try SEARCH()

https://docs.microsoft.com/en-us/dax/search-function-dax

[amitchandak]

@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"

[harshnathani]

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)

[pranit828]

HI @bilingual 

Not very clear.

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