Forum Discussion

jalaomar's avatar
jalaomar
Helper IV
3 years ago

check if one column value equals another column value

Hi,   I would like to check if the Value Project Classification/Applicant is the same or not for the ID, tried with the follwoing DAX but doesn't work. What am I doing wrong?    
Need Help
ToddChitt's avatar
ToddChitt
Super User
3 years ago

The table from which you are creating this calculated DAX column needs to be on the MANY side of a one-to-many relationship with the table/column in the RELATED function, and it appears that is not the case. 

 

If I have three tables, City, State, and Country, then from the City table I can write a funtion like this:

MyCountry = RELATED('Country'[Country Name]) and DAX can navigate up the tables from City to State and from State to Country to know where you are.

Buy if I try it the other way around: from the Country table:

MyCity = RELATED('City'[City Name]) 

Then DAX can't figure that out because there are MANY Cities and MANY States in each ONE Country.

Or if there are NO relationships between the two.

 

Check your Model Design and make sure you have the right relationships defined.

 

  • jalaomar's avatar
    jalaomar
    Helper IV
    3 years ago

    Hi,

     

    I try to create One to Many relation but PBI automaticall changes to Many to One 

    do you know what that is?

     

    also is there a different way to manage this comparision between two columns?

     

    BR

    J

    • ToddChitt's avatar
      ToddChitt
      Super User
      3 years ago

      Your screen shots are very small. Can you copy and paste the formula as text, and also show us the relationship between the two tables?

       

      Regarding switching from ONe-to-Many to Many-to-One:

      Power BI will inspect the cardinality of the two columns when you drag and drop a join between them. Using my example above, if I drag from StateID of my State table to the StateID of the City table, Power BI will create a One-to-Many relatonship with State table shown at the top, and City table at the bottom. It mean: For every ONE State ID (in the top table), that value can show up MANY times in the City table (bottom). But if I drag the other way around (from City to State tables on State ID), my relationship will be Many-to-One (MANY State ID's in City table (top) can show up ONE time in State table (bottom) ). It's the same thing really.

      It's like this: ONE Parent can have MANY children (one-to-many) or MANY children can have the same Parent (Many-to-one).

      • jalaomar's avatar
        jalaomar
        Helper IV
        3 years ago

        Hi,

         

        this is the formual, it's created in the ZCV_PROJ_PROFITABILITY table.

         

        Column = IF(RELATED('Governance Dimesions'[Applicant Text])=ZCV_PROJ_PROFITABILITY[Applicant Text],1,0)
         
        and this is the current relationship between the tables