left or Other Solution

[netanel]

Hi All,

I have a table that contains accounts by levels
In the measure I created, I create another column that will define that if the beginning of one of the accounts begins with the following numbers(5,6,7,8,9), then the column will write -1 otherwise 1

My problem is that I can only do this for one column.
Is it possible to do this for all the columns in the table?
Is there a smarter solution?

Agg +-  = IF(LEFT(EpmAccount[AccountLvl9],1) = "5"&&"6"&&"7"&&"8"&&"9",-1,1)

Thanks!

[ValtteriN]

Hi,

If the amount of account columns is manageable I would use SWITCH + TRUE and LEFT+ in like this:

Otherwise I would add a new table to the model where you have defined the logic in PQ or e.g. in excel and then use this as a basis for mapping.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[netanel]

Hi @ValtteriN ,

Your measure is excellent and helped me in this case half a solution

But I used IF to try to change the plus or minus from one hierarchy to another
The hierarchy are in the same table in column AccountLvl2

IF AccountLvl2  is PBI then the formula we wrote
And so on

Thanks!

[ValtteriN · Accepted solution]

Hi,

I am not 100% I understood your goals. Anyway when it comes to defining balance sheet and profit and loss accounts functions such as PATH + PATHITEM are quite useful (you could use these to define all sorts of hierarchial logics with key pairs. Additionally I would look into enriching the data at account level either in PQ or with DAX .e.g you could create a custom column in PQ which would contain start and end account based on the range values you have and then expand this to multiple rows thus creating +/- information on account level. The main benefit of doing this on account level is that you could ignore some of the challenges posed by hierachy. Alternatively you could create the hierarchy logic entirely within the SWITCH. 

I hope these suggestions/ideas help you with the issue at hand!