Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Filter 2 Columns In A Table Into A Single Column With True/False

Greetings All -    I've recently inherited a PowerBI desktop report where I need to make some changes. From a high level, I need to compare two columns in a single table and return a value of 'True...
Need Help
amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , in DAX

if(not(isblank([EMPLOYEE_ID])) && Not(isblank( [E_PRIMARY_EMPLOYEE_ID] )) && [EMPLOYEE_ID] =[E_PRIMARY_EMPLOYEE_ID]  , true(), false() )

 

in power query

 

if [EMPLOYEE_ID] <> null  and [E_PRIMARY_EMPLOYEE_ID] <> null  and  [EMPLOYEE_ID] =[E_PRIMARY_EMPLOYEE_ID]  then true else  false  

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Amit - 

     

    This is getting closer but it still returns a value even when one or both columns have blank values in them. Is there a way to perform this comparison but leave the cell blank if one or both columns contain a blank?

    • v-xiaotang's avatar
      v-xiaotang
      Community Support
      4 years ago

      Hi Anonymous 

      If you want to perform this comparison but leave the cell blank, try this,

      DAX code:

      Column = 
IF (
    NOT ( ISBLANK ( 'Table'[EMPLOYEE_ID] ) )
        && NOT ( ISBLANK ( 'Table'[E_PRIMARY_EMPLOYEE_ID] ) )
            && 'Table'[EMPLOYEE_ID] = 'Table'[E_PRIMARY_EMPLOYEE_ID],
    1,
    BLANK ()
)

      M code:

      if [EMPLOYEE_ID] <> null and [E_PRIMARY_EMPLOYEE_ID] <> null  and  [EMPLOYEE_ID] =[E_PRIMARY_EMPLOYEE_ID]  then 1 else  null  // M code, in power query

       

       

       

       

      Best Regards,

      Community Support Team _Tang

      If this post helps, please consider Accept it as the solution to help the other members find it more quickly.