Related function resulting blank columns

[penguins_fly]

Hi guys,

I am a newbie here. I am trying to create a new table using the Related() function. 

DAX code:

Sales in USD = 
ADDCOLUMNS(
    Sales,
    "Country Name", RELATED(Countries[Country]),
    "Exchange Rate", RELATED('Exchange Data'[Exchange Rate]),
    "Exchange Currency", RELATED('Exchange Data'[Exchange Currency]))

But all the newly generated columns are blank. I made sure the connection was one to many between the tables. Please help me find my mistake here.

[Anonymous]

Hi @penguins_fly ,

Thanks for the reply from @amitchandak , please allow me to provide another insight:

I have tested this based on the information you have provided and the test results are correct.

Below are the steps of my test:

First, here are my three data tables and their relationship models.

I applied your DAX syntax and this is the new table generated.

You can check this in several ways:

Can you confirm that the relationship between the tables is indeed active and oriented correctly?

Have you verified the data integrity and data types of key columns?

Make sure that there are no mismatches or null values in the key columns used to establish the relationship.

Verify that the data types used to establish relationships between tables are correct. A common mistake is to use one column as text and another as a number, which prevents a successful match.

The Related function works by pulling data from the "one" end of a one-to-many relationship to the "many" end. Make sure the direction of the Relationship is set correctly.

If you have any further questions please feel free to contact me.

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

The PBIX file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[penguins_fly]

Hi @Anonymous 

Thank you for the reply.

The relationships and data types are correct.

There are no missing values or errors in the key columns either.

The DAX I used is:

Sales in USD = 
ADDCOLUMNS(
    Sales,
    "Country Name", RELATED(Countries[Country]),
    "Exchange Rate", RELATED('Exchange Data'[Exchange Rate]),
    "Exchange Currency", RELATED('Exchange Data'[Exchange Currency]),
    "Gross Revenue USD", [Gross Revenue] * RELATED('Exchange Data'[Exchange Rate]),
    "Net Revenue USD", [Net Revenue] * RELATED('Exchange Data'[Exchange Rate]),
    "Total Tax USD", [Total Tax] * RELATED('Exchange Data'[Exchange Rate]) )

Yesterday it gave me blank columns and today I got the following error

and then when I saved the file, the error was gone and the table was generated. Can you tell me what's going on here? Am I missing out on something or is this a version issue?

Pls find the file link: Sample file 

I am totally new to Power BI. Any help would be appreciated.

Best regards,

[Anonymous · Accepted solution]

Hi @penguins_fly ,

Thank you very much for your prompt reply.

I can not open your pbix link, please upload it another way, and please do not be logged into your own account when uploading.

Or you could provide me with sample data for testing, please remove any sensitive data in advance.

I am not sure about your [Gross Revenue], [Net Revenue], [Total Tax] measure syntax, I created my own measure example:

Gross Revenue = MIN('Exchange Data'[Exchange Currency])

Net Revenue = MAX('Exchange Data'[Exchange Currency])

Total Tax = SUM('Sales in USD'[Exchange Currency])

After applying your DAX syntax, there is still no error reported, which is the page effect:

I think you are right, this could be a temporary glitch, perhaps you could try updating Power BI Desktop to the latest version.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[amitchandak]

@penguins_fly , I think, when you are using add columns, the new table is created, which is not related to other tables. First, add the columns to sales table and then use them