Forum Discussion

leeleeds123's avatar
leeleeds123
Frequent Visitor
2 years ago
Solved

Number Matching Columns - Tried all the obvious things!

Hi all,

 

I have two columns, one with colleague ID, one with manager ID.

 

I am trying to calculate how many direct reports each person has, i am trying to debug an issue where i can't seem to generate a match between two columns with numbers in.

 

Match = IF('Management Hierarchy Report'[Column1] = 'Management Hierarchy Report'[Column2],1,0)
 
For every row it is returning 0. 
 
I have checked to make sure both columns have the same format - they do they are both whole numbers.
 
I have tried trimming and cleaning. 
 
When i hard code in a number into my calc it finds the match as expected, there is clearly an issue with formatting but i can't think of what it could be.
 
Really struggling to think of what else to try as all of the obvious stuff hasn't helped.
 
Any suggestions???
Need Help
  • SamWiseOwl's avatar
    SamWiseOwl
    2 years ago

    Hi leeleeds123 

    Could you do the in the query editor using a conditional column?
    That uses M instead of DAX and is before the data is encoded into the model.

    This also gives you the option of using Group By with a Count or Distinct count to work out how many each have.

     

    Finally have you tried doing a LEN on the cell?

    I once brought data in from an API and it included a Non-breaking space which meant my two columns didn't match!

     

2 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Icon for Super User rankSuper User
    2 years ago

    Hi leeleeds123 

    Could you do the in the query editor using a conditional column?
    That uses M instead of DAX and is before the data is encoded into the model.

    This also gives you the option of using Group By with a Count or Distinct count to work out how many each have.

     

    Finally have you tried doing a LEN on the cell?

    I once brought data in from an API and it included a Non-breaking space which meant my two columns didn't match!

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    2 years ago

    leeleeds123 I assume this is a column (Match) and is this being created in the table 'Management Hierarchy Report'? Can you share sample data?