cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
snifer Member
Member

check if same currency

  
company namecurrency
unoeur
dueeur
treusd

 

let's say my table like this

i make filtering based on company column:

if i select company "uno"it will say that currency dispayed is "eur"

but now if a make multiple selections, I want to create a measure that will check if all selection have the same currency

so If i select "uno' and "tre" will say error difference currency

1 ACCEPTED SOLUTION

Accepted Solutions
d_gosbell Senior Member
Senior Member

Re: check if same currency

Sorry, I cut and pasted your examples and did not notice the syntax error in your selectedvalue call. The closing bracket is in the wrong position, it should look like the following:

 

CCheck = IF(
COUNTROWS(VALUES(UploadAccountsCurrentyPeriod[GroupStructure.CurrencyCode]))=1
&& SELECTEDVALUE(CurrencySelector2[value])="Local Currency", VALUES(UploadAccountsCurrentyPeriod[GroupStructure.CurrencyCode])
, "ERROR Multiple Currencies"
)
7 REPLIES 7
Highlighted
d_gosbell Senior Member
Senior Member

Re: check if same currency

You could do this in a measure with an expression like the following:

 

= IF( COUNTROWS(VALUES(Table1[currency]))=1, VALUES(Table1[currency]), "ERROR Mulitple Currencies")
 
replacing "table1" for the real name of your table
snifer Member
Member

Re: check if same currency

@d_gosbell 

 

I adapted your script and it is work,

but now I need to add a condition, I try to add but it doesn't work, maybe you can give a look

i need to ahh that need to make this check just in case that "Local Currency" is Selected

i was thinking of

IF(SELECTEDVALUE(CurrencySelector[value]="Local Currency"),)

so fill script looks like:

 

CCheck = IF(SELECTEDVALUE(CurrencySelector[value]="Local Currency"),) && IF( COUNTROWS(VALUES(UploadAccountsCurrentyPeriod[GroupStructure.CurrencyCode]))=1, VALUES(UploadAccountsCurrentyPeriod[GroupStructure.CurrencyCode]), "ERROR Mulitple Currencies")))
 
but give me a syntax error don't know why
d_gosbell Senior Member
Senior Member

Re: check if same currency

but give me a syntax error don't know why

Well that would probably be because this

 

IF(SELECTEDVALUE(CurrencySelector[value]="Local Currency"),)

 

is not valid. IF expects at least 2 arguments and you are only supplying 1.

 

I'm not sure I really understand your requirement. But if you want to check that both CurrencySelector[value] = "Local Currency" and that there is only 1 GroupStructure.CurrencyCode then something like the following might work:

 

CCheck = IF(
COUNTROWS(VALUES(UploadAccountsCurrentyPeriod[GroupStructure.CurrencyCode]))=1
&& SELECTEDVALUE(CurrencySelector[value]="Local Currency")
  , VALUES(UploadAccountsCurrentyPeriod[GroupStructure.CurrencyCode])
  , "ERROR Multiple Currencies"
)

snifer Member
Member

Re: check if same currency

@d_gosbell 

 

yes that is my goal

CCheck = IF(
COUNTROWS(VALUES(UploadAccountsCurrentyPeriod[GroupStructure.CurrencyCode]))=1
&& SELECTEDVALUE(CurrencySelector2[value]="Local Currency"), VALUES(UploadAccountsCurrentyPeriod[GroupStructure.CurrencyCode])
, "ERROR Multiple Currencies"
)

 

using your formula I got this error:

 

A single value for column 'value' in table 'CurrencySelector2' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Capture4.PNGas you see there is just 1 column 1 row

d_gosbell Senior Member
Senior Member

Re: check if same currency

Sorry, I cut and pasted your examples and did not notice the syntax error in your selectedvalue call. The closing bracket is in the wrong position, it should look like the following:

 

CCheck = IF(
COUNTROWS(VALUES(UploadAccountsCurrentyPeriod[GroupStructure.CurrencyCode]))=1
&& SELECTEDVALUE(CurrencySelector2[value])="Local Currency", VALUES(UploadAccountsCurrentyPeriod[GroupStructure.CurrencyCode])
, "ERROR Multiple Currencies"
)
snifer Member
Member

Re: check if same currency

@d_gosbell 

Now the check working, but if it is not selected local currency is displaying always "ERROR Multiple Currencies"

 

I need to add the case if the local currency is not selected

 

SELECTEDVALUE(CurrencySelector2[value]) not "Local Currency"

 

to do not do the check and just display "ok"

d_gosbell Senior Member
Senior Member

Re: check if same currency


I need to add the case if the local currency is not selected 

 to do not do the check and just display "ok"


Oh, OK, this was not clear to me from your earlier post. Moving the check for "Local Currency" to an outer IF like the following should do what you want.

 

CCheck =
IF( SELECTEDVALUE(CurrencySelector2[value])="Local Currency", "Ok",
  IF(
    COUNTROWS(VALUES(UploadAccountsCurrentyPeriod[GroupStructure.CurrencyCode]))=1
    , VALUES(UploadAccountsCurrentyPeriod[GroupStructure.CurrencyCode])
    , "ERROR Multiple Currencies"
  )
)