Help with the IF measure

[netanel]

Hi everyone,

I have a measure that progresses to the whole thing
I'm just missing the next piece

In the measure I say
For specific accounts to be in + or - according to column (1,-1)
The importance varies depending on the AccountLvl2 column
So if I chose PBI in column AccountLvl2
I will receive accounts with plus and minus and if I chose Budgetry I will receive plus and minus in different amounts

Agg +- = SWITCH(TRUE(),

LEFT(EpmAccount[AccountLvl3],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl3Desc],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl4Desc.2],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl4],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl5Desc],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl5],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl6Desc],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl6],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl7Desc],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl7],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl8],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl8Desc],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl9],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl9Desc],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl99],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl99Desc],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl10Desc],1) in {"5","6","7","8","9"},-1,

LEFT(EpmAccount[AccountLvl10],1) in {"5","6","7","8","9"},-1,1)

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[netanel]

Hi All,

I am trying to create a correct plus and minus sign in my Amount column
I have an agg column(+,-) where the signs are correct
And I have an Amount column.
When I multiply between them something falls

The logic of the measure says Amount * Agg column sign
The rest of the formula is ignoring other filters

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[netanel]

@lbendlin 

I combined the two posts into one because it's a similar problem with a similar solution
I have attached a clean PBIX file

https://1drv.ms/f/s!AonyYI-TdspHgUhwngZ6OKvpnSsc

In the file you can find DIM Account which contains the measure column i started writing
The measure gives me a half answer

The problem:
I want to give each account negative or positive Amount but it varies from hierarchy to hierarchy
The hierarchies are in Dim Account under the AccountLvl2 column
The calculated column I created is in Dim Account under the Agg +- column

Thank you very much for your help!

[lbendlin]

Thank you for providing the PBIX.  Unfortunately it is way too complex.  If you want to get assistance you will need to slim your sample file down to only cover the issue you are asking about. Please remove anything that is not directly involved in your question.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[netanel]

I uploaded a slimmer version
I am available privately if anything is unclear
Thanks @lbendlin 

[netanel · Accepted solution]

@lbendlin 

This is the answer 

its give me half answer but i keep going from there

Thanks for now

Sign =

IF(LEFT(Epm_Fct[Account],1)="5",-1,

    IF(LEFT(Epm_Fct[Account],1)="6",-1,

        IF(LEFT(Epm_Fct[Account],1)="7",-1,

             IF(LEFT(Epm_Fct[Account],1)="8",-1,

                 IF(LEFT(Epm_Fct[Account],1)="9",-1,

1)))))

[lbendlin]

If you want you can refactor that.

Sign = IF(LEFT(Epm_Fct[Account],1) in {"5","6","7","8","9"},-1, 1)