DAX function to check if data is consistent

[Anonymous]

Hi there!

I have a huge table with employee data from two data sources (SharePoint, SAP).

My data looks like this:

ID

Name

CC

Entry date

Leaving date

Div

BU

Quitting reason

Quitting type

Work hours

FTE

Location

Position

DataSource

563475

Paul Smith

731021

2019. 03. 04.

2021. 04. 18.

Finance

MCCK

resignation

Voluntary

40

1

Toronto

Application Administrator

SharePoint

563475

Paul Smith

731021

2019. 03. 04.

2021. 04. 18.

Finance

MCCK

resignation

Voluntary

40

1

Toronto

Application Administrator

SAP

I need to compare the data from the two sources. I need a function that compares the two rows for each employee and checks if there's difference in any column apart from the DataSource. 

The output should be in a new column that indicates if there's an inconsistency in the data. It would be nice if there's a possibility to color the cells with the deviations or something like this.

Any idea how to achive this with DAX?

Thanks a lot.

[CNENFRNL]

DAX is for calculation whereas PQ is for such data cleansing/shaping tasks.

[tamerj1]

@Anonymous 
Not sure what exactly you're looking for but following is one way of doing that.

Differences? = 
VAR DistinctRows =
    COUNTROWS (
        DISTINCT (
            FILTER (
                ALLEXCEPT ( 'Employee Data', 'Employee Data'[DataSource] ),
                'Employee Data'[ID] = SELECTEDVALUE ( 'Employee Data'[ID] )
            )
        )
    )
RETURN
    IF ( DistinctRows = 2, "Yes", "" )

[Anonymous]

I get the following error trying your solution: 

A circular dependency was detected: Employee_Data[Differences?].

The output should be a new column in the data source table as you did it. 

It would be useful to have an additional column, which is empty by default, but in case of difference it would show the name of the column that differs.

[tamerj1 · Accepted solution]

@Anonymous 
This is the code for a calculated column

Any Differences? = 
VAR DistinctRows =
    COUNTROWS (
        DISTINCT (
            CALCULATETABLE (
                SELECTCOLUMNS (
                    'Employee Data',
                    "@Name", [Name], "@CC", [CC], "@EntryDate", [Entry date], "@LeavingDate", [Leaving date],
                    "@@Div", [Div], "@BU", [BU], "@QuittingReason", [Quitting reason], "@QuittingType", [Quitting type],
                    "@WorkingHours", [Work hours], "@FTE", [FTE], "@Location", [Location], "@position", [Position]
                ),
                ALLEXCEPT ( 'Employee Data', 'Employee Data'[Name] )
            )
        )
    )
RETURN
    IF ( DistinctRows = 2, "Yes", "" )

[tamerj1]

@Anonymous 
This is a measure code not a calculated column.

[tamerj1]

Hi @Anonymous 

How does the report look like? or just the same as the data sorce table?