If function, Related function does not work

[Anonymous]

Hello togehter,

I have a problem and don´t know what to do.

I have a sales report (SAP Sales Analysis) where are listed the customers, their postal codes, the country ID and the revenues.

Then I got a second report where I have the responsible org. units (e.g. Workshop Berlin, Workshop Hamburg, Fieldservice North...). With a crossjoin I get nearly every responsible org. unit in my sales report table and can show, which org. unit was responsible for which customer.

At least I have three postal code tables for three countries. There are listed every postal code and the service areas (e.g. Areas South, Area North, Area South-West...). In one service area can be many responsible org. units (-> Area South has the responsible org. units Fieldservice South, Workshop Munich, Workshop XY...).

What I want to do is that Power BI should look in the postal code list for Germany, when in the sales report the customer has the country ID "DE", and give me the service area for the customer.

So I thought the function would be like that:

 IF([Country ID]="DE"; RELATED or LOOKUPVALUE(´Postal Codes Germany´[Service Area]; IF([Country ID]="BE"; RELATED or LOOKUPVALUE(´Postal Codes Belgium´[Service Area]; IF([Country ID]="NL"; RELATED or LOOKUPVALUE((´Postal Codes Netherlands´[Service Area]; 0 )))

But the measure can´t find the row "Country ID" and I don´t know why. The measure was created in the table where the row exists.

The postal code tables are in a relationship with the sales report table in both directions.

Do you know where the error is or what can I do instead of a measure to create a column in the sales report with the Service Areas from the three postal code tables?

Thanks!

[v-sihou-msft · Accepted solution]

@Anonymous

In this scenario, you should create a calculated column with LOOKUPVALUE(). I assume you build the relationship between Sales and each Postal Code table on [Postal Code] column

=
SWITCH (
    Sales[Country ID],
    "DE", LOOKUPVALUE (
        'Postal Codes Germany'[Service Area],
        'Postal Codes Germany'[Postal Code], Sales[Postal Code]
    ),
    "BE", LOOKUPVALUE (
        'Postal Codes Belgium'[Service Area],
        'Postal Codes Belgium'[Postal Code], Sales[Postal Code]
    ),
    "NL", LOOKUPVALUE (
        'Postal Codes Netherlands'[Service Area],
        'Postal Codes Netherlands'[Postal Code], Sales[Postal Code]
    ),
    BLANK ()
)

Regards,

[Anonymous]

@v-sihou-msft,

that works good. Is it possible to say in this column that the Postal Code XY is related to the Service Area North as well?

With a seperate column or the if function because of the datatype variant does not work.

Thanks and greetings