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

[Anonymous]

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.

[amitchandak]

@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]

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]

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.

[ALLUREAN]

Hi, @Anonymous 

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

[Anonymous]

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.