Concatenate filtered values

[Anonymous]

Hi everybody,

I'm quite new to PowerBI and wasn't able to solve the following problem:

I have two tables (BeneficiariesIndicators and BeneficiaryIndicatorsName) that are related (1:n) through a primary key (ID resp. BeneficiaryIndicatorId):

I would want to concatenate the IDCode with the IndicatorName in the table "BeneficiariesIndicators" (I've tried to create a new column in the table "BeneficiariesIndicators") but since the IndicatorName exists in 4 different languages, PowerBI doesn't know which value to select. The LanguageId I would want it to display is "1" (formatted as a whole number).

Any ideas?

[MFelix]

Hi @Anonymous ,

Try the following calculated column:

Beneficiaries_Indicator_Name =
CONCATENATE (
    'BeneficiariesIndicator'[IDCode];
    LOOKUPVALUE (
        'BeneficiariesIndicatorNames'[IndicatorName];
        'BeneficiariesIndicatorNames'[BeneficiariesIndicatorID]; 'BeneficiariesIndicator'[ID];
        'BeneficiariesIndicatorNames'[LanguageID]; 1
    )
)

[Anonymous]

Hi MFelix and thanks for your quick response! It would probably work if it wasn't for: "Function 'LOOKUPVALUE' is not allowed as part of calculated column DAX expressions on DirectQuery models.". Is there a way to work around this?

[MFelix]

Hi @Anonymous ,

Did not know it was a direct query try the following code:

Column 2 =
CONCATENATE (
    'BeneficiariesIndicator'[IDCode];
    CALCULATE (
        MAX ( BeneficiariesIndicatorNames[IndicatorName] );
        FILTER (
            BeneficiariesIndicatorNames;
            BeneficiariesIndicatorNames[BeneficiariesIndicatorID] = BeneficiariesIndicator[ID]
                && BeneficiariesIndicatorNames[LanguageID] = 1
        )
    )
)

[Anonymous · Accepted solution]

Hi @MFelix 

Thanks again for your help. I've now solved it the "rookie way" 😉

1. I created a new table and linked it to the 'BeneficiariesIndicators' ->

BENI Name EN = filter('BeneficiariesIndicatorNames', [LanguageId]=1)

2. I looked up the IDCode ->

IDCode = LOOKUPVALUE(BeneficiariesIndicators[IDCode],BeneficiariesIndicators[Id],'BENI Name EN'[BeneficiariesIndicatorId])

3. I concatenated the IDCode & Name ->

Code & Name = [IDCode] & " - "& [IndicatorName]