Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Concatenate filtered values

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?

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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]

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    6 years ago

    Hi Anonymous ,

     

    Try the following calculated column:

    Beneficiaries_Indicator_Name =
CONCATENATE (
    'BeneficiariesIndicator'[IDCode];
    LOOKUPVALUE (
        'BeneficiariesIndicatorNames'[IndicatorName];
        'BeneficiariesIndicatorNames'[BeneficiariesIndicatorID]; 'BeneficiariesIndicator'[ID];
        'BeneficiariesIndicatorNames'[LanguageID]; 1
    )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        MFelix
        Super User
        6 years ago

        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
        )
    )
)