Forum Discussion

penguins_fly's avatar
penguins_fly
Regular Visitor
2 years ago
Solved

Related function resulting blank columns

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.

 

Interesting Links
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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!

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      penguins_fly
      Regular Visitor
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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!