Return value which has been filtered in DAX

[Anonymous]

Hi all,

I am trying to find a way to manually set target goals for my KPI visual based on a filtered value (base currency). The goals for each currency are as follows:

I can get the goals to work for one given currency (say GBP), by using the code

ActDocsRedTarg = IF(HASONEVALUE(KPI2[ActiveDocsRed]), VALUES(KPI2[ActiveDocsRed]), CALCULATE(MINA(KPI2[ActiveDocsRed]), KPI2[CurrencyCode] = "GBP"))

and it appears as it should

But this isn't very useful when the currency code is say USD where the goals should be 1250 and 2500 as I have had to hardcode GBP into the DAX.

Is there a way I can write the last bit of code to say something like:

... KPI2[CurrencyCode] = (Value which has been filtered)

Thanks in advance!

[Vvelarde]

@Anonymous 

hi, try with SELECTEDVALUE.

Regards

Victor

[Anonymous]

I have rearranged the code for ease. And GeneralLedgerStructure[BaseCurrency] is the field being filtered and only contains currency values from the previous post.

I tried with SELECTEDVALUE but it just returned the error:

"A function 'SELECTEDVALUE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

This is my code

ActDocsRedTarg = 
IF(
    HASONEVALUE(KPI2[ActiveDocsRed]), 
    VALUES(KPI2[ActiveDocsRed]), 
    CALCULATE(
        MINA(KPI2[ActiveDocsRed]), 
        KPI2[CurrencyCode] = SELECTEDVALUE(GeneralLedgerStructure[BaseCurrency])
    )
)

[Vvelarde · Accepted solution]

@Anonymous 

Try adding a FILTER in your formula.

ActDocsRedTarg = 
IF(
    HASONEVALUE(KPI2[ActiveDocsRed]), 
    VALUES(KPI2[ActiveDocsRed]), 
    CALCULATE(
        MINA(KPI2[ActiveDocsRed]), FILTER(KPI2,
        KPI2[CurrencyCode] = SELECTEDVALUE(GeneralLedgerStructure[BaseCurrency])
    )
)

[Anonymous]

It works!

Thank you!