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' if the values match. If not, then the value would be 'False' - the trues and falses could be 1's and 0's, or yes's and no's if that works better. However, both columns contain blanks (or nulls) and I only want to perform the comparison on the two columns where the cells have numerical values in them. I need this to happen on a row by row basis. The previous owner created a conditional column to perform the comparison, but didn't filter out the blanks in each column thus creating false "True's" so to speak. The M code and screenshot are below:

 

In PowerQuery, this is what it looks like:

 

What I'd like to do is turn the conditional column "Matching Primary and Billed" into a calculated column using a single DAX expression that compares the values in EMPLOYEE_ID and E_PRIMARY_EMPLOYEE_ID and return a True if they match, a FALSE if they don't and only for the values in both columns that have a numerical value in them (ignoring the nulls/blanks).  I'm guessing some combination of IF(ISBLANK and/or FILTER functions may be involved but I'm uncertain of the correct syntax in this case. Any guidance would be appreciated.

 

Thank you.

 

 

 

Need Help

5 Replies

  • ALLUREAN's avatar
    ALLUREAN
    Solution Sage
    4 years ago

    Hi, Anonymous 

     

    Did you try first clean the columns (trim, remove duplicates)?

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Allurean. Unfortunately, I can't remove rows where one or both of those columns contain a blank value in the cell as other measures/columns in the report use those rows.

  • 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.